• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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

0
ubsmail
Asked:
ubsmail
1 Solution
 
AmmarRCommented:
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

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24505879.html


good luck
0
 
batchakamalCommented:
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)
0
 
parthmalhanCommented:
Use the following query:

You can change the column in order by caluse to whatever column you want and sort it desc or asc
Top 1 always returns the first row according to the rows returned.
select Top 1 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'
order by buyprice asc

Open in new window

0
 
anillucky31Commented:
can you share table structure for these two tables buyprice, stock ?
0
 
GhunaimaCommented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now