SELECTing first of Duplicates - 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)

SELECTing first of Duplicates

Author

Topic

mwanstall
Starting Member

1 Post

Posted - 2008-06-26 : 21:19:57

This is slightly different than the usual duplicates problem, I have
the following issue:

dbo.Prices
----------------

id | CustID | ProdID | PriceDate | Price
--------------------------------------------------------
1    42          5      1/1/2008    4.00
1    42          5      1/1/2008    5.60
--------------------------------------------------------

I want to ONLY select the FIRST instance of duplicate fields id,
CustID, ProdID and PriceDate even though the Price field is different.

We basically have an unclean Price table that SHOULD have a 1 to 1
relationship (only 1 price per CustID/ProdID/PriceDate combination)
but we don't...all 'best practise' aside, I have to produce a report
on this data and have been told the first price that gets pulled is
good enough.

Any help is really appreciated!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 21:29:36

[code]
select *
from (
select *,
row_no = row_number() over (partition by id, CustID, ProdID, PriceDate order by Price)
from Prices
)
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources