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

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

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

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