Returning distinct data without using DISTINCT

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)

Returning distinct data without using DISTINCT

Author

Topic

bh885796
Starting Member

2 Posts

Posted - 2007-03-14 : 11:05:39

Lets say i have a view set up that contains data about the history of a vhs tape.

The view has multiple records for each tape number sorted by Date DESC.

I need to grab only the records for the most recent for each tape.

So for example i have...

TapeNum | DateReturned | ReturnedBy
1 02/03/2007 Joe Customer
1 02/01/2007 Sally Consumer
2 03/05/2007 Gerald Moviegoer
2 03/02/2007 Joe Customer

Is there a simple query i can use to do this?

I've tried using DISTINCT in alot of ways, but it doesn't return what i need. I need the entire record for 1 occurance of each tape.

any help would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 11:08:38

[code]
select *
from table1 t
where datereturned = (select max(datereturned) from table1 x where x.tapenum = t.tapenum)
[/code]

bh885796
Starting Member

2 Posts

Posted - 2007-03-14 : 11:13:26

OMG! Thank you so much. That did the trick.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:14:21

select t1.tapenum, t1.datereturned, t1.returnedby
from table1 as t1
inner join (select tapenum, max(datereturned) as dr from table1 group by tapenum) as x on x.tapenum = t1.tapenum and x.dr = t1.datereturned

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources