Web Development

Ask something
Discussion Locked

SQL2k - Selecting from duplicate records

0 helpful
5 replies

by timp999 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I've got a pickle here, that I don't know how to solve. In a SQL2000 database, I need to build a query on one table that contains multiple records for the same key.

here's the deal: I have multiples of the same ID, with different timestamps. for a key of ID,Timestamp I need to select the ones that have the highest time value. I've been trying to use the max function but I'm by no means a SQL expert and I'm stuck.

Can anybody offer some guidance? I know this should be a simple querybut it's killing me.

Thanks!
Tim

5 replies

by nikki96

id field: myId
timestamp field: myTimestamp
table name: myTbl

select myId, myTimestamp
from myTbl t
where myTimestamp =
(select max(t2.myTimestamp) from myTbl t2 where t.id=t2.id)

by madestroitsolutions

SELECT TOP 1 * FROM YourTable WHERE IDField = ID ORDER BY TimeStampfield DESC

Very simple, you can also select more than 1 record by changing the "TOP 1" clause to "TOP #". This query will first index the data by timestamp descending, It will getall the items that you have matching an specific ID, and then it will return the TOP # (in this case 1) items.

Hope that helps.

Good Luck!
Questions or comments, please feel free to write to: juanja01@optonline.net

by timp999

Thank you!

I got what I needed fromt he first post, but I'll tuck this one away for future use.

This discussion is closed to new replies.