Pulling lowest prices from quer with duplicate ids

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

Pulling lowest prices from quer with duplicate ids

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-05-03 : 17:35:44

I'd like to query a database table that has some unique catalogids and some catalogids that are repeated multiple times, but only pull the lowest price item if they have the same catalogid. I need to keep the results in a uniqueorder sequence.

So the basic query is this.
Select uniqueorder, catalogid, price from inventory order by uniqueorder.

What would a modified query look like that would only pull the item with the lowest price if they have the same catalogid?
Thanks!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-05-03 : 18:10:11

this?

select i.uniqueorder, i.catalogid, x.minprice
from inventory i
join (select catalogid, min(price) as minprice from inventory group by catalogid) x on x.catalogid=i.catalogid
order by i.uniqueorder

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 19:34:49

[code]
select uniqueorder, catalogid, price
from
(
Select uniqueorder, catalogid, price,
row_number() over (partition by catalogid order by price) as rn
from inventory
)t
where rn=1
order by uniqueorder
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources