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