query construction question - SQL Server Forums

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)

query construction question

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2008-03-17 : 17:49:21

hi there,

i need some help/advice on the following:

i have a table in which i keep cycle counts for various machine parts we use. so for all sense and purposes the table looks like this:

ID,part,date,cyclecount

now because there are a lot of parts here with a lot of dates, i am trying to see if there is a way to write a query which will retrieve the latest row (based on the date) for ALL distinct parts. it could be something simple but i am not sure how to do it and any help will be appreciated.

thanks

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2008-03-17 : 23:04:36

Kindly provide DDL and expected result for your needs.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 07:39:51

SELECT t1.*
FROM PartsTable t1
INNER JOIN (SELECT part,MAX(date) as Latest
FROM PartsTable
GROUP BY part) t2
ON t2.part=t1.part
AND t2.Latest=t1.date

But you will still get duplicates if you've more than 1 record for same part with same date value.

ronin2307
Posting Yak Master

126 Posts

Posted - 2008-03-18 : 09:09:29

i think this is it but i have to verify
there will never be more than one record for a given part on the same date (in a sense) because the timestamp is included as well.

quote:
Originally posted by visakh16

SELECT t1.*
FROM PartsTable t1
INNER JOIN (SELECT part,MAX(date) as Latest
FROM PartsTable
GROUP BY part) t2
ON t2.part=t1.part
AND t2.Latest=t1.date

But you will still get duplicates if you've more than 1 record for same part with same date value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 09:29:18

quote:
Originally posted by ronin2307

i think this is it but i have to verify
there will never be more than one record for a given part on the same date (in a sense) because the timestamp is included as well.

quote:
Originally posted by visakh16

SELECT t1.*
FROM PartsTable t1
INNER JOIN (SELECT part,MAX(date) as Latest
FROM PartsTable
GROUP BY part) t2
ON t2.part=t1.part
AND t2.Latest=t1.date

But you will still get duplicates if you've more than 1 record for same part with same date value.

Ok. test it and let us know the outcome.

ronin2307
Posting Yak Master

126 Posts

Posted - 2008-03-18 : 11:17:27

i think it will work...thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources