Revisions to Fetch the rows which have the Max value for a column for each distinct value of another column

deleted 15 characters in body

Source Link

edited Sep 23, 2008 at 20:31

boes

2.9k
2
23
28

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

results:

userid      value       date                                                   
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

results:

userid      value       date                                                   
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

results:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

added 537 characters in body

Source Link

edited Sep 23, 2008 at 20:14

boes

2.9k
2
23
28

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

results:

userid      value       date                                                   
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates

select T1.userid, T1.value, T1.date from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2
    where T1.userid= T2.userid and T1.date = T2.maxdate

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

results:

userid      value       date                                                   
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

added 136 characters in body

Source Link

edited Sep 23, 2008 at 14:50

boes

2.9k
2
23
28

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates

select T1.userid, T1.value, T1.date from theTableusers T1,
    (select max(date) as maxdate, userid from theTableusers group by userid) T2
    where T1.userid= T2.userid and T1.date = T2.maxdate

select T1.userid, T1.value, T1.date from theTable T1,
    (select max(date) as maxdate, userid from theTable) T2
    where T1.userid= T2.userid and T1.date = T2.maxdate

(T-SQL) First get all the users and their maxdate. Join with the table to find the corresponding values for the users on the maxdates

select T1.userid, T1.value, T1.date from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2
    where T1.userid= T2.userid and T1.date = T2.maxdate

Source Link

answered Sep 23, 2008 at 14:39

boes

2.9k
2
23
28

Loading

Collectives™ on Stack Overflow

Return to Answer

Post Timeline