a simple (hopefully) sql question.

hello,
QUESTION 1:
I have a table with 3 columns: ID, GroupID, Date.
eg:
ID GROUPID DATE
1 10 2004-03-02
2 10 2004-03-01
3 20 2004-03-03
4 20 2004-03-01
I want to select all records from the table (grouped by GroupID), and get
the ID of the record with the MAX Date in each group.
How can i do this?
Thanks in advance."tracy" <tracyy@.spamfreenyf.com> wrote in message
news:Wvnwc.15020$wd7.1675@.front-1.news.blueyonder.co.uk...
> hello,
> QUESTION 1:
> I have a table with 3 columns: ID, GroupID, Date.
> eg:
> ID GROUPID DATE
> 1 10 2004-03-02
> 2 10 2004-03-01
> 3 20 2004-03-03
> 4 20 2004-03-01
>
> I want to select all records from the table (grouped by GroupID), and get
> the ID of the record with the MAX Date in each group.
> How can i do this?
>
> Thanks in advance.
Assume table T
SELECT T.*
FROM T
INNER JOIN
(SELECT groupid, MAX(date) AS max_date
FROM T
GROUP BY groupid) AS D
ON T.groupid = D.groupid AND T.date = D.max_date
JAG|||Ok, but what if I had same dates for the same groupID.
I would want to return the MAX(ID) in that scenario.
"John Gilson" <jag@.acm.org> wrote in message
news:WNnwc.107940$Nn4.23425233@.twister.nyc.rr.com...
> "tracy" <tracyy@.spamfreenyf.com> wrote in message
> news:Wvnwc.15020$wd7.1675@.front-1.news.blueyonder.co.uk...
get[vbcol=seagreen]
> Assume table T
> SELECT T.*
> FROM T
> INNER JOIN
> (SELECT groupid, MAX(date) AS max_date
> FROM T
> GROUP BY groupid) AS D
> ON T.groupid = D.groupid AND T.date = D.max_date
> --
> JAG
>|||"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:Ohowc.15200$wd7.9034@.front-1.news.blueyonder.co.uk...
> Ok, but what if I had same dates for the same groupID.
> I would want to return the MAX(ID) in that scenario.
SELECT MAX(id) AS id, T.groupid, T.date
FROM T
INNER JOIN
(SELECT groupid, MAX(date) AS max_date
FROM T
GROUP BY groupid) AS D
ON T.groupid = D.groupid AND T.date = D.max_date
GROUP BY T.groupid, T.date
JAG

> "John Gilson" <jag@.acm.org> wrote in message
> news:WNnwc.107940$Nn4.23425233@.twister.nyc.rr.com...
> get
>