Get the latest date for each ID

1 Sep 2006 12:55 PM

Brandon H. Campbell

Hello,

I'm using SQL 2000.

I have a table with an ID and dates plus other fields.

I was trying to get the latest date for each ID. I tried a simple grouping
asking for the MAX(Date) and I still get all the dates. I tried writing a
nested query and still get the same answers.

For example, if you have a list of books and you wanted the latest
publication date for each edition of those books, how would you go about
doing that.

--
Brandon H. Campbell
Systems Analyst
Database Administrator

1 Sep 2006 12:59 PM

Aaron Bertrand [SQL Server MVP]

SELECT ID, MAX(Date)
FROM table
GROUP BY ID

Show quote

"Brandon H. Campbell" <BrandonHCampb***@discussions.microsoft.com> wrote in
message news:8C9150DA-BB15-4DC3-B760-5AFFC1636562@microsoft.com...
> Hello,
>
> I'm using SQL 2000.
>
> I have a table with an ID and dates plus other fields.
>
> I was trying to get the latest date for each ID. I tried a simple grouping
> asking for the MAX(Date) and I still get all the dates. I tried writing a
> nested query and still get the same answers.
>
> For example, if you have a list of books and you wanted the latest
> publication date for each edition of those books, how would you go about
> doing that.
> --
> Brandon H. Campbell
> Systems Analyst
> Database Administrator
>

1 Sep 2006 1:35 PM

Brandon H. Campbell

Thank you very much.

I made the mistake of including the date in the group by clause.

I'm not having a great mental day.

--
Brandon H. Campbell
Systems Analyst
Database Administrator

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT ID, MAX(Date)
> FROM table
> GROUP BY ID
>
>
> "Brandon H. Campbell" <BrandonHCampb***@discussions.microsoft.com> wrote in
> message news:8C9150DA-BB15-4DC3-B760-5AFFC1636562@microsoft.com...
> > Hello,
> >
> > I'm using SQL 2000.
> >
> > I have a table with an ID and dates plus other fields.
> >
> > I was trying to get the latest date for each ID. I tried a simple grouping
> > asking for the MAX(Date) and I still get all the dates. I tried writing a
> > nested query and still get the same answers.
> >
> > For example, if you have a list of books and you wanted the latest
> > publication date for each edition of those books, how would you go about
> > doing that.
> > --
> > Brandon H. Campbell
> > Systems Analyst
> > Database Administrator
> >
>
>
>

1 Sep 2006 1:09 PM

Tracy McKibben

Brandon H. Campbell wrote:
Show quote

> Hello,
>
> I'm using SQL 2000.
>
> I have a table with an ID and dates plus other fields.
>
> I was trying to get the latest date for each ID. I tried a simple grouping
> asking for the MAX(Date) and I still get all the dates. I tried writing a
> nested query and still get the same answers.
>
> For example, if you have a list of books and you wanted the latest
> publication date for each edition of those books, how would you go about
> doing that.

http://www.realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com