CODEWITHSUNDEEP
phpmysql
rockstardev
rockstardev

Reputation: 13537

SQL query to select only the maximum items?

I have this table: I want to search by UID

ID | VID | UID
1  |  1  | 5
1  |  1  | 6
1  |  2  | 6
2  |  3  | 5
2  |  3  | 6
2  |  4  | 6

I want to end up with this result:

ID | VID | UID
1  |  2  | 6
2  |  4  | 6

In other words, only select the entries where the VID is MAX of the UID but keeping in min NID could differ. Something like this I suppose:

select * from TABLE where uid = 6 and max(vid)

???

But this doesn't work?

Upvotes: 0

Views: 244

Answers (3)

Rich Adams
Rich Adams

Reputation: 26574

One way is to order by the value in descending order (so the max is at the top), then just select the first result.

SELECT t.ID,
       t.VID,
       t.UID 
FROM table t 
WHERE t.ID = 1 
ORDER BY t.VID DESC 
LIMIT 1

Or do you mean you want all rows where t.VID is the highest value? In which case you could do something like this,

SELECT t.ID,
       t.VID,
       t.UID
FROM table t
WHERE t.ID = 1
      AND t.VID = (SELECT MAX(VID) FROM table);

EDIT: Based on the edit to your question, it looks like you just want the max VID value for each ID? If I'm understanding you correctly, then this should give you what you need.

SELECT t.ID,
       max(t.VID) as VID,
       t.UID 
FROM table t 
WHERE t.UID = 6 
GROUP BY t.ID

Upvotes: 3

Cathy
Cathy

Reputation: 67

I expect your real-life example is more complicated (at least has more data).

This query will give you the row you want.

SELECT id,vid, uid FROM TABLE where id = 1 and vid in (select max(vid) from TABLE where id = 1)

Upvotes: 0

weltraumpirat
weltraumpirat

Reputation: 22604

You need to have a subquery. This should work:

select * from TABLE where ID='1' AND VID=(select max(VID) from TABLE)

Upvotes: 1

Related Questions