CODEWITHSUNDEEP
sql
ALC
ALC

Reputation: 65

SQL select multiple max rows where ID is same

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

Upvotes: 3

Views: 30417

Answers (6)

Marcus Vinicius Pompeu
Marcus Vinicius Pompeu

Reputation: 1261

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
    )

Upvotes: 0

Rohan
Rohan

Reputation: 3078

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.

Upvotes: 0

Justin
Justin

Reputation: 9724

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 |

Upvotes: 9

Avi Turner
Avi Turner

Reputation: 10456

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...

Upvotes: 0

cosmos
cosmos

Reputation: 2303

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;

Upvotes: 0

Taj
Taj

Reputation: 1728

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

Upvotes: 1

Related Questions