a simple (hopefully) sql question.  
Author Message
slooper





PostPosted: Sat Jun 05 12:55:02 CDT 2004 Top

SQL Server >> 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.

SQL Server166  
 
 
John





PostPosted: Sat Jun 05 12:55:02 CDT 2004 Top

SQL Server >> 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.

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





PostPosted: Sat Jun 05 13:28:32 CDT 2004 Top

SQL Server >> a simple (hopefully) sql question. Ok, but what if I had same dates for the same groupID.

I would want to return the MAX(ID) in that scenario.






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





PostPosted: Sat Jun 05 13:40:34 CDT 2004 Top

SQL Server >> a simple (hopefully) sql question.

> 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





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