MySQL SELECT Precedence

UVAdcock

I'm trying to select records from a database on multiple conditions:

SELECT * FROM current WHERE group_name = \"$group_name\" AND smr = \"A\" limit 2

Some don't fit the condition and I'd like to select records where smr = B or C or D until I get at least 2 records where the group name is the same.

Basically, if they're aren't two records in the same group that have an smr of A, then I'd like to get the ones in the group that have an smr of B, or C, or D -- basically the two top-ranked records in that group. What can I do?

TIA,
Justin

tomhath

I didn't test it, but this seems like it should work. If the order isn't exactly what you need, you could change it to order by a CASE condition:

SELECT * FROM current
WHERE group_name = \"$group_name\"
AND smr in ("A", "B", "C", "D")
ORDER BY smr
limit 2

[deleted]

Interesting query.

So you want to have the first two records where SMR is the same value...
so if your list was
a,b,c,e,a, you'd get A

and

b,c,d,c,a would get C?

This won't do:
SELECT * FROM current
WHERE group_name = \"$group_name\"
AND smr in ("A", "B", "C", "D")
ORDER BY smr
limit 2

because you'd get the first two records where SMR is A,B,C or D, not where both are A or both as B etc...

I fear you may have to do four queries to get the 'relative position' of the topmost SMR=A records and the topmost SMR=B etc, and compare those to decide which of ABCD is the highest, and fetch those.

Either that or hefty subqueries/internal variables

tomhath

because you'd get the first two records where SMR is A,B,C or D, not where both are A or both as B

I think he wants the first two smr values (e.g. "b,c,d,c,a" should give A and 😎. The group_name will always be the same ($group_name). Unless I misunderstand the original question.

tomhath

But then again, I see vincente's interpretation.

In that case (and without subselects) he'll need at least two queries. How about something like this to get the smr (again untested)?

SELECT group_name , smr, count() FROM current
WHERE group_name = \"$group_name\"
AND smr in ("A", "B", "C", "D")
group by group_name , smr
having count() >= 2
order by smr, group_name
limit 1

[deleted]

Yes but how do you get the 'first pair that exists' bit in there?

From what I understand he wants the first pair of records where group=X and SMR is on of A, B, C or D.

So the problem is not how do you get the SMR values that exist in two or more records, but how do you work out which SMR value appears twice first.