asked on

Selecting single rows

I need to select one row per number field where there can be multiple rows per number for the table. Suggesitons? Group does not work because it affects the buyprice.

select buyprice.number, unit_price, mixgroup

from buyprice
join stock on stock.number=buyprice.number 

where NOT (stock.units = 0 AND stock.discont = 1) and stock.number not like 'P-%' and stock.number not like '9-%' and buyprice.supplier<>'UBS'

Open in new window

AmmarR

Dear if you were using sql 2005 or above

i would have suggested using Partition by and Rank

http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

they will serve your need, but for sql 2000 there is no straight forward method.

check these work arounds

http://www.eggheadcafe.com/software/aspnet/31792105/over--partition-by--how-can-i-do-something-similar-in-sql2000.aspx

https://www.experts-exchange.com/questions/24505879/Translate-ROW-NUMBER-OVER-PARTITION-BY-to-SQL-Server-2000-query.html

good luck

batchakamal

Try this..

select buyprice.number, unit_price, mixgroup
from buyprice
join stock on stock.number=buyprice.number
where NOT (stock.units = 0 AND stock.discont = 1) and stock.number not like 'P-%' and stock.number not like '9-%' and buyprice.supplier<>'UBS'

AND buyprice.number = (SELECT TOP 1 buyprice.number FROM buyprice buy WHERE buy.unit_price = buyprice.unit_price AND buy.mixgroup = buyprice.mixgroup)

ASKER CERTIFIED SOLUTION

Parth Malhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anillucky31

can you share table structure for these two tables buyprice, stock ?

G Trurab Khan

Try using "select distinct buyprice.number, unit_price, mixgroup"

This will work only if unit_price & mixgroup are same for each (buyprice.number) If there are multiple values then you have to use group function to get output in a single row per buyprice.number. You may use
"select buyprice.number, min(unit_price) unit_price_from, max(unit_price) unit_price_to, min(mixgroup) mixGroup_From, max(mixgroup) mixGroup_To
......
group by buyprice.number