SQL select multiple max rows where ID is same

Question

i've struggled again and again on this, but can't get it to work. been on these forums for hours now... Data set:

Row Date            AccountID   Column 1    
1   21/02/2013 0:30     A0M8FA1003YP    .       
2   21/02/2013 0:30     A0M8FA1003YP    .       
3   21/02/2013 0:30     A0M8FA1003YP    .       
4   24/09/2007 12:00    A0M8FA1003YP    .       
5   24/09/2007 12:00    A0M8FA1003YP    .       
6   24/09/2007 12:00    A0M8FA1003YP    .       
7   12/02/2009 12:00    A023123332YP    .       
8   24/09/2003 12:00    A023123332YP    .       
9   24/09/2003 12:00    A023123332YP    .       
10  24/09/2003 12:00    A023123332YP    .

I want to return the max value of the date column, but not just return a single row, but any rows that match that max value. I.e. In the above set I want to return rows 1, 2, 3 and 7 (all columns for the rows as well).

Row Date                AccountID       Column 1    
1   21/02/2013 0:30     A0M8FA1003YP    .       
2   21/02/2013 0:30     A0M8FA1003YP    .       
3   21/02/2013 0:30     A0M8FA1003YP    .   
7   12/02/2009 12:00    A023123332YP    .

I've got thousands of rows, and the number of matching rows to return for each ACCOUNTID will vary, some 1, some 2, some 10. Please help me!!!

UPDATE Have also tried this

Select max(ASS_SCH_DATE) over (partition by AccountID), 
       AccountID, 
       ASS_SCH_DATE, 
       ACCOUNTID 
from #Temp3 
order by #Temp3.ACCOUNTID

Results still showing extra rows.


(No column name)            ASS_SCH_DATE                ACCOUNTID
2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP
2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP
2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP
2013-02-21 00:30:00.000     2007-09-24 12:00:00.000     A0M8FA1003YP
2013-02-21 00:30:00.000     2007-09-24 12:00:00.000     A0M8FA1003YP

[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) — 1615903, Jun 17 '13 at 05:31
hi, i've tried everything including things such as: SELECT *,C_ENDORSEMENTID+'-'+ACCOUNTID as 'C3' into #Temp3 FROM #Temp2 WHERE ASS_SCH_DATE IN (SELECT MAX(ASS_SCH_DATE) FROM #Temp2 group by ACCOUNTID) order by ACCOUNTID — ALC, Jun 17 '13 at 05:48
Thanks very much team! I've stuck with justins as there's very few lines involved. — ALC, Jun 17 '13 at 06:26

score 8 · Accepted Answer · answered Jun 17 '13 at 06:09

Query:

SQLFIDDLEExample

SELECT t1.*
FROM Table1 t1
WHERE t1.Date = (SELECT MAX(t2.Date)
                 FROM Table1 t2
                 WHERE t2.AccountID = t1.AccountID)

Result:

| ROW |                            DATE |    ACCOUNTID |
--------------------------------------------------------
|   1 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   2 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   3 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   7 | February, 12 2009 12:00:00+0000 | A023123332YP |

Thanks Justin, this works as well: Amended Script: SELECT t1.* FROM #Temp3 t1 WHERE t1.ASS_SCH_DATE = (SELECT MAX(t2.ASS_SCH_DATE) FROM #Temp3 t2 WHERE t2.ACCOUNTID = t1.ACCOUNTID) order by ACCOUNTID — ALC, Jun 17 '13 at 06:24

score 1 · Answer 2 · answered Jun 17 '13 at 05:33

1

select * from table where date in (select max(date) from table)

answered Jun 17 '13 at 05:33

Taj

1,718
1
12
16

1

thanks bunny, but this only selects the first instance for the first account in the table. There are thousands of ACCOUNT ID sets and related date values. – ALC Jun 17 '13 at 05:39
1

just look if you have all the account ID's related to exact max same date – Taj Jun 17 '13 at 05:40
Hi Bunny, sorry, can't understand that. I've updated the title to show a few more rows of the table so you get the picture. – ALC Jun 17 '13 at 05:43

cosmos · Answer 3 · 2013-06-17T06:00:56.653

0

Windows functions are what you are looking for:

Select row,max(date) over (partition by AccountID) As max_date, AccountID, 
Column1 from table where max_date = date;

edited Jun 17 '13 at 06:00

answered Jun 17 '13 at 05:47

cosmos

2,263
1
17
30

Hi again - i'm still seeing rows that should be there, i've updated the topic with new results – ALC Jun 17 '13 at 05:54
Updated my answer, try now. – cosmos Jun 17 '13 at 06:01
does it now serves your purpose?? – cosmos Jun 17 '13 at 06:06

Rohan · Answer 4 · 2013-06-17T06:14:38.940

0

Try this,

select * from tablename where date in (select max(date) from table group by AccountID)

Please see the sqlFiddle demo.It is giving same output that you are expecting.

edited Jun 17 '13 at 06:14

answered Jun 17 '13 at 05:50

Rohan

3,068
1
20
26

Hi RS, have tried it, still getting same results (see OP) Query is: select * from #Temp3 where ASS_SCH_DATE in (select max(ASS_SCH_DATE) from #Temp3 group by AccountID). Could there be something else getting in the way here? – ALC Jun 17 '13 at 05:58
I have added link showing same output that you are expecting. – Rohan Jun 17 '13 at 06:22

score 0 · Answer 5 · answered Jun 17 '13 at 06:03

0

Try using Ranks:

SELECT  AccountID, 
        ASS_SCH_DATE, 
        RANK() OVER (PARTITION BY ASS_SCH_DATE ORDER BY AccountID) DateRank
FROM YourTable
WHERE DateRank = 1

This way you can also select highest ranks, second highest etc...

answered Jun 17 '13 at 06:03

Avi Turner

10,234
7
48
75

Thanks Avi, it works, amended script. WITH RankedData AS ( SELECT *, RANK() OVER (PARTITION BY ACCOUNTID ORDER BY ASS_SCH_DATE desc) as Ranking FROM #Temp3 ) SELECT RankedData.Ranking, RankedData.* FROM RankedData WHERE RankedData.Ranking = 1 ORDER BY RankedData.ACCOUNTID – ALC Jun 17 '13 at 06:24

score 0 · Answer 6 · answered Jun 17 '13 at 06:38

0

Easy...

Being 'dummy' your table, I would write:

SELECT dummy.*
FROM
  (
      SELECT MAX(Date) Date, AccountID
      FROM dummy
      GROUP BY AccountID
  ) max_date
    INNER JOIN dummy
    ON(
          dummy.Date      = max_date.Date
      AND dummy.AccountID = max_date.AccountID
    )

Or, using modern, more semantical syntax:

WITH max_date(Date, AccountID)
AS
(
  SELECT MAX(Date) Date, AccountID
  FROM dummy
  GROUP BY AccountID
)
SELECT dummy.*
FROM
  max_date
    INNER JOIN dummy
    ON(
          dummy.Date      = max_date.Date
      AND dummy.AccountID = max_date.AccountID
    )

answered Jun 17 '13 at 06:38

Marcus Vinicius Pompeu

1,219
1
11
24

I've created dummy as: CREATE TABLE dummy( Row INT, Date DATETIME, AccountID VARCHAR(255), [Column 1] VARCHAR(255) ) GO insert into dummy values(1 , '20130221 00:30', 'A0M8FA1003YP', '.') insert into dummy values(2 , '20130221 00:30', 'A0M8FA1003YP', '.') insert into dummy values(3 , '20130221 00:30', 'A0M8FA1003YP', '.') insert into dummy values(4 , '20070924 12:00', 'A0M8FA1003YP', '.') insert into dummy values(5 , '20070924 12:00', 'A0M8FA1003YP', '.') insert into dummy values(6 , '20070924 12:00', 'A0M8FA1003YP', '.') ... – Marcus Vinicius Pompeu Jun 17 '13 at 06:39

SQL select multiple max rows where ID is same

6 Answers6

Linked