Help with a Max unique part query

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

Transact-SQL (2000)

Help with a Max unique part query

Author

Topic

sqlteamuser
Starting Member

2 Posts

Posted - 2003-08-01 : 14:57:53

Hi,

I have a little problem with a Max unique part query.
I am trying to get result that shows all fields for each unique part the most recent date

In my parts table I have:
Date_time, part_num, cost, district

2003-07-20 10:10:00, part1, 10, 1
2003-07-20 10:11:00, part1, 20, 2
2003-07-20 10:11:00, part2, 30, 2
2003-07-20 10:11:00, part3, 20, 2

I tried:
Select max(Date_Time), part, cost, district
From Data_Part
Group By Date_Time , part, cost, district

Shows all of the columns

What is the query to show all fields for each unique part the most recent date? That would show:
2003-07-20 10:11:00, part1, 20, 2
2003-07-20 10:11:00, part2, 30, 2
2003-07-20 10:11:00, part3, 20, 2

Thanks in advance,

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-01 : 15:23:58

You can try this


SELECT	a.*
FROM	Data_Part a join
	(
	SELECT	MAX(Date_Time)'MaxDate', part
	FROM Data_Part
	GROUP BY part
	) b ON a.part = b.part AND a.date_time = b.MaxDate

but trying this using datetime is yucky....

sqlteamuser
Starting Member

2 Posts

Posted - 2003-08-01 : 16:19:01

Thanks alot,
That worked.

I just wanted to find out how did you know that you needed to use a subquery.

Thanks again,

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-07 : 08:36:55

Years of trial and error

...trial and error...

and even more trial and error.

I have also read some T-SQL books and visit this site often.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources