Getting unique records - 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

General SQL Server Forums

New to SQL Server Programming

Getting unique records

Author

Topic

dbansal
Starting Member

2 Posts

Posted - 2009-07-15 : 15:07:37

My table (serials) has 3 columns Serial Number, Model, Date Sold.

If a serial number is sold, returned and sold again, there will be 2 records in this table for that serial number with different date sold.

I want to run a query where i read the serial number from another table and pick up the last record from this table i.e when the last time this serial number was sold.

For example

Select * from serials where serlNumber in
(select serlNumber from tempTable)

This returns all the records for that serial number. I want the last record based on date for that serial number.

How would i do that. Any help is greatly appreciated.

Thanks

-D

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-15 : 15:13:18

Which version of sql server?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-15 : 15:35:27

2005 or later:
select * from
(
Select
*,
row_number() over (partition by serlNumber order by your_date_sold_column desc) as rownum
from serials where serlNumber in
(select serlNumber from tempTable)
)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

dbansal
Starting Member

2 Posts

Posted - 2009-07-15 : 15:47:59

That worked. I m using SQL 2005.

Thanks A lot.

-D

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-15 : 15:49:09

my pleasure