Urgent Help - 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 2005 Forums

Transact-SQL (2005)

Urgent Help

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-03-19 : 14:46:31

Hi

I have a table which looks like the following:

ID Postcode PUBID PubName Postcode Dist
3961 AB10 1QS N1Q0063 Star & Garter AB11 6HB 0.087
3961 AB10 1QS N1U0004 CafÃ© Drummond AB10 1JR 0.138
3961 AB10 1QS 8CS8316 Noose & Monkey AB25 1NQ 0.223
3961 AB10 1QS J1G0642 Malt Mill AB10 6BY 0.439
3961 AB10 1QS J1G0314 Tilted Wig AB11 5BA 0.472
3961 AB10 1QS N1Q0041 Brig O'Dee AB10 7JN 1.476
3961 AB10 1QS N1Q0052 Greentrees AB21 7AA 5.447
3956 AB10 1QT N1Q0063 Star & Garter AB11 6HB 0.062
3956 AB10 1QT N1U0004 CafÃ© Drummond AB10 1JR 0.087
3956 AB10 1QT 8CS8316 Noose & Monkey AB25 1NQ 0.263
3956 AB10 1QT J1G0314 Tilted Wig AB11 5BA 0.416
3956 AB10 1QT J1G0642 Malt Mill AB10 6BY 0.485
3956 AB10 1QT N1Q0041 Brig O'Dee AB10 7JN 1.501
3956 AB10 1QT N1Q0052 Greentrees AB21 7AA 5.483
3507 AB10 1UA J1G0642 Malt Mill AB10 6BY 0.196
3507 AB10 1UA N1Q0063 Star & Garter AB11 6HB 0.316
3507 AB10 1UA 8CS8316 Noose & Monkey AB25 1NQ 0.334
3507 AB10 1UA N1U0004 CafÃ© Drummond AB10 1JR 0.416

If you notice there are multiple 'Dist' values for one ID.

Is there a way of just showing the record with the lowest 'Dist' value for each ID?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-19 : 15:13:21

Here is one way:


select * 
from <Yourtable> t
where Dist = 
	(select top 1 dist from <Yourtable> where id = t.id order by Dist)

EDIT:
Here is another (2005 way). I don't know which will perform better


select	ID
	,Postcode 
	,PUBID 
	,PubName 
	,Postcode 
	,Dist
from	(
	select	ID
		,Postcode 
		,PUBID 
		,PubName 
		,Postcode 
		,Dist
		,row_number() over (partition by ID order by Dist) rn
	from	yourTable t
	) d
where	rn = 1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources