group by with value of another column
I've got table Articles
ID identity autoincement, IDArticle: nvarchar(100) ,IDCar nvarchar(100), createddate
How to convert this:
SELECT IDCar , MAX(createddate)
FROM Article开发者_如何学Cs
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: Sql query number of occurance
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
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)
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
精彩评论