CODEWITHSUNDEEP
sqldb2aggregate-functions
ForeverStudent
ForeverStudent

Reputation: 2537

How to filter rows based on criteria

Consider this toy example where I have a very simple SQL table containing some partnumbers, prices and currency. I want to find the lowest price for each item.

Hers is table PRICELIST

PartNumber     Price               Currency
1              19                  USD
1              10                  CAD
1              18                  GBP
2              15                  USD
2              14                  CAD
2              8                   GBP
3              5                   USD
3              1                   CAD
3              11                  GBP

I want to show the lowest price with the currency. This is the output I want:

PartNumber     Price               Currency
1              10                  CAD
2              8                   GBP
3              1                   CAD

if I say select partnumber, min(price) from pricelist group by partnumber

the query will execute, but if I specify the currency:

select partnumber, min(price),currency from pricelist group by partnumber

Then I get an error saying:

An expression starting with "CURRENCY" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified..

I want to display the currency value for the row that has the lowest price. What should I do?

database is DB2.

By the way, this is a highly simplified example, in my actual query I have left joins to create larger sets, if that matters

Upvotes: 0

Views: 132

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Just use row_number():

select pl.*
from (select pl.*,
             row_number() over (partition by partnumber order by price) as seqnum
      from pricelist
     ) pl
where seqnum = 1;

If there are ties for the lowest price, this chooses an arbitrary one. For all of them, use rank() or dense_rank() instead of row_number().

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You have to join back to the original table so as to get the rest of the fields:

select t1.partnumber, t1.price, t1.Currency
from pricelist as t1
join (
   select partnumber, min(price) as price
   from pricelist 
   group by partnumber
) as t2 on t1.partnumber = t2.partnumber and t1.price = t2.price

Alternatively you can use ROW_NUMBER:

select partnumber, price, Currency
from (
  select partnumber, price, Currency,
         row_number() over (partition by partnumber 
                            order by price) as rn
  from pricelist ) as t
where t.rn = 1

Note: The first method may select more than record per partnumber (in case of ties), whereas the second method always selects one record per partnumber.

Upvotes: 1

Related Questions