CODEWITHSUNDEEP
sqlsql-server
mahesh cs
mahesh cs

Reputation: 335

SQL query to select groups in groups

Hello I am unable to figure out the way for writing SQL query for following table and scenario

Lets say we have following table viz. LowestBid

ItemID      SuppplierID  LowestBid

A1                 S1               10
A1                 S2               20
A1                 S3               30
B1                 S2               10
B1                 S3               20
C1                 S3               10
C1                 S1               20
C1                 S2               30

Lets say we have a function which has parameter viz. N , the out put of intended SQL query is like e.g. N=1. For N=1, we need to find one supplier for each item whose bid value is lowest for the same item.

ItemID      SuppplierID  LowestBid

A1                 S1               10
B1                 S2               10
C1                 S3               10

If N=2 For N=2, we need to find one suppliers for each item whose bid value is lowest and second lowest bid value for the same item.

ItemID      SuppplierID  LowestBid

A1                 S1               10
A1                 S2               20
B1                 S2               10
B1                 S3               20
C1                 S3               10
C1                 S1               20

Upvotes: 0

Views: 51

Answers (1)

Josh Smeaton
Josh Smeaton

Reputation: 48720

If you're using postgres or another rdbms that has analytic/window functions, you can use row_number to filter for the rows you're interested in:

select
  q.item_id,
  q.supplier_id,
  q.bid
from (
select
  item_id,
  supplier_id,
  bid,
  row_number() over (partition by item_id order by bid) as row_num
from bids) q
where q.row_num <= 2 -- N=2;

Produces:

ITEM_ID SUPPLIER_ID BID
1       1           10
1       2           20
2       3           10
2       1           20
3       2           10
3       3           20

See the fiddle here: http://sqlfiddle.com/#!3/ce3a1/1

Upvotes: 2

Related Questions