Just the latest row please . . .

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Just the latest row please . . .

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-07 : 10:12:44

Justin writes "If I have a table with data as so:-


Item	Location 	Qty	Date
Apple	Bedroom		3	12/31/2001 12:00:00
Orange	Closet		18	11/05/2001 15:34:00
Banana	Kitchen		59	01/28/2002 15:04:00
Apple	Kitchen		3	01/01/2002 02:00:00
Banana	Cellar		53	02/01/2002 18:04:00
Apple	Bathroom	2	02/01/2002 14:31:00

Can I create a single SQL query to return all columns with just the latest date for the Item?

That is, I only want to see the following:-

Item	Location 	Qty	Date
Orange	Closet		18	11/05/2001 15:34:00
Apple	Bathroom	2	02/01/2002 14:31:00
Banana	Cellar		53	02/01/2002 18:04:00

I was thinking of 2 queries - SELECT DISTINCT Item then stick it in an array, then for each array element pull out the latest date. But thats too slow . . .

ps/ I'm using Windows 2000 and SQL Server 7"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-07 : 10:38:46

Try this.

SELECT A.*
FROM Table1 A
JOin (Select Item, Max(Date)AS "date"
FROM Table1
GROUP BY Item) AS B
ON A.Item = B.Item AND A.Date = B.Date

The Inner finds the lastest date for each item. Join that back to the table to find the details for that item and date.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-02-07 : 11:44:50

Joins are usually faster but you could also use a correlated subquery.


SELECT a.*
from table1 a
where a.date in (select max(date) from table1 b where a.item = b.item)

Justin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources