CODEWITHSUNDEEP
sql
shaster
shaster

Reputation: 13

SQL query from highest value from duplicates

I have a table that looks like this.

username | vendor | time
-------------------------
x        |  a     | 10
x        |  b     | 11
x        |  c     | 12
y        |  a     | 9
y        |  c     | 14

I want to pull out the rows with the highest time value among their usernames, so it should look like this:

username | vendor | time
-------------------------
x        |  c     | 12
y        |  c     | 14

I have no idea how to go about this after a couple queries didn't get what I wanted. What would this look like? Thanks!

Upvotes: 0

Views: 45

Answers (3)

CalvT
CalvT

Reputation: 3213

I'm not sure that the OP wants a vendor filter, so the following will get the top time for each username, and the linked vendor.

This is making the same assumption as Jacobm001 that it is MySQL

select * from (select username, vendor, time from foo order by time desc) as t
group by username

Where foo is the name of your table

SQL Fiddle

Upvotes: 0

McGlothlin
McGlothlin

Reputation: 2099

This code is fairly database agnostic:

SELECT username, vendor, MAX(time)
FROM foo
WHERE vendor = 'c'
GROUP BY username, vendor;

If it were me, I would want to use an analytic function (personal preference, really). This is supported in both MS SQL Server and Oracle:

SELECT username, vendor, MAX(time) OVER (PARTITION BY username, vendor)
FROM foo
WHERE vendor = 'c';

Here is a SQL Fiddle.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use the ANSI standard window function row_number():

select t.*
from (select t.*,
             row_number() over (partition by username order by time desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 0

Related Questions