group by with value of another column

Question

Hi,

I've got table Articles

ID identity autoincement, IDArticle: nvarchar(100) ,IDCar nvarchar(100), createddate

How to convert this:

SELECT IDCar , MAX(createddate)
FROM Articles
GROUP BY IDCar 

to get IDArticle eg:

1 art1 BWM 5-21-2010
2 art2 BMW 5-24-2010
3 art3 BMW 5-31-2010
4 art4 Porshe 5-31-2010
5 art5 Porshe 6-1-2010

Expecting result is:

art3
art5

It's not duplicated with: http://stackoverflow.com/questions/2736769/sql-query-number-of-occurance/2736809#2736809

Answer 1

SELECT outerTable.IDArticle
FROM Articles outerTable
WHERE outerTable.createddate = 
    (SELECT MAX(innerTable.createddate) 
     FROM Articles innerTable
     WHERE outerTable.IDCar = innerTable.IDCar
     GROUP BY innerTable.IDCar) 

Answer 2

SELECT a.IDArticle
FROM
(
  SELECT IDCar , MAX(createddate) as max_date
  FROM Articles
  GROUP BY IDCar 
) max
INNER JOIN Articles a ON a.IDCar = max.IDCar and a.createddate = max.max_date

Answer 3

WITH ArticlesMaxDate (IDCar, MaxCreatedDate) AS
(
    SELECT 
        IDCar , MAX(createddate) AS MaxCreatedDate
    FROM 
        Articles 
    GROUP BY 
        IDCar 
)
SELECT 
    a.IDcar
   , a.IDArticle
   , amd.MaxCreatedDate
FROM
   Articles a
   INNER JOIN ArticlesMaxDate amd ON amd.IDCar = a.IDCar
      AND amd.MaxCreatedDate = a.createddate

Answer 4

Use this:

select IDArticle 
from articles o
where createdate = 
    (select max(createdate) from articles where IDCar = o.IDCar)