CODEWITHSUNDEEP
phpmysql
bayblade567
bayblade567

Reputation: 376

mysql second priority where clause

So for example I have a table users, with a column 'count' and a column 'uid' which is the primary key.

uid | count
1   |  20
2   |  20
3   |  20
4   |  20
4   |  18

I want to select exactly one row which has count less than or equal to the present row. For example, I have the row where uid = 2.

Now I want to select a column which has count less than or equal to the present count value which is "20". and I want to select exactly one row which is closest to it.

Now I will have the choice to select either the row which has uid = 3 or uid = 4. In such case, I will want to select the column with the lowest uid value such that it is greater than the present uid value which is 2. Therefore I will want uid = 3 as my result.

How to put this in a mysql query ?

Upvotes: 0

Views: 97

Answers (2)

Alex
Alex

Reputation: 17289

SELECT * FROM users
WHERE count <= 20
  AND uid IN (3,4)
ORDER BY  uid ASC,count DESC
LIMIT 1

Upvotes: 0

Joel Hinz
Joel Hinz

Reputation: 25374

So something like this?

SELECT * FROM users
WHERE count <= 20
ORDER BY count DESC, uid ASC
LIMIT 1

That'll sort the results so that everything above 20 is discarded, and you get the rest in decreasing count order, with lower user ids taking priority if there are multiples of the same count. The LIMIT 1 restricts the query to return only one row.

If you want to make the comparison to an existing row, your easiest bet is to do this:

SELECT * FROM users
WHERE count <= 20
  AND uid != 2
ORDER BY count DESC, uid ASC
LIMIT 1

Upvotes: 1

Related Questions