SQL Server get column not in Group By clause?

Question

How to get the following result from this table?

ID1|ID2| Date  
----------------------
1  | 1 | 01-01-2014  
1  | 2 | 02-01-2014  
2  | 3 | 03-01-2014

I want to get ID1 & ID2 for the maximum date when grouped by ID1

Result:

1,2  
2,3

My code:

SELECT 
    ID1, MAX(DATE) 
FROM 
    Table 
GROUP BY 
    ID1

I need something like

SELECT 
    ID1, ID2, MAX(DATE) 
FROM 
    Table 
GROUP BY 
    ID1

Can someone help me?

Answer 1

There's three ways to do it.

One, a subquery:

SELECT t1.ID1, t1.ID2, t2.MAX_DATE
FROM Table t1
INNER JOIN (
    SELECT ID1, MAX(DATE) AS "MAX_DATE" FROM Table GROUP BY ID1) t2
    ON t1.ID1 = t2.ID2

Or you can use the OVER() clause if you're on SQL Server 2005+, recent versions of Oracle, or PostgreSQL (and most recent things not MySQL or MariaDB):

SELECT ID1,
    ID2,
    MAX(DATE) OVER(PARTITION BY ID1)
FROM Table

Or you can use a correlated subquery:

SELECT t1.ID1, 
    t1.ID2, 
    (SELECT MAX(DATE) FROM Table WHERE ID1 = t1.ID1)
FROM Table t1

Answer 2

You can accomplish this by joining the table to the aggregate, like this:

SELECT t.*
FROM 
    Table t 
     INNER JOIN 
      (
        SELECT 
            ID1, 
            MAX(Date) MaxDate 
        FROM Table 
        GROUP BY ID1
      ) MaxDate ON
        t.ID1 = MaxDate.ID1 AND
        t.Date = MaxDate.MaxDate

Answer 3

you can use ROW_NUMBER analytic function

SELECT *
FROM
(SELECT *,
      ROW_NUMBER() over ( partition by ID1 order by [date] desc) as seq
 FROM Table1
) T
WHERE T.seq =1