Limiting rows from a joined table

Question

I have 2 tables Part and PartDtl. PartDtl contains one row for each order that has been placed for that part.

PartNum | Description | ...
1234    | 1234 desc   | ...
2345    | 2345 desc   | ...

PartNum | Qty | DueDate     | ...
1234    | 12  | 2015/11/15  | ...
1234    | 66  | 2015/11/17  | ...
1234    | 45  | 2015/11/18  | ...
1234    | 33  | 2015/11/16  | ...
2345    | 77  | 2015/11/12  | ...
2345    | 43  | 2016/01/01  | ...

I use the follwing sql to get the data:

select 
  p.PartNum, 
  pd.Quantity,
  pd.DueDate
from 
  Erp.Part as p, 
  Erp.PartDtl pd
where
  p.Company = pd.Company and 
  p.PartNum = pd.PartNum and 
  pd.StockTrans = 'TRUE'
order by 1, 3, 2

The problem is that some parts have lots of orders placed and I only need to see the first few. For example:

PartNum | Qty | DueDate     
1234    | 12  | 2015/11/15  
1234    | 33  | 2015/11/16  
1234    | 66  | 2015/11/17  
2345    | 77  | 2015/11/12  
2345    | 43  | 2016/01/01

I haven't been able to figure out how to have the first 3 orders due of each part.

I have tried a sub-query, but haven't figured out the syntax.

Can you show me the way, please.

nb - there are other fields which are shown in the sql which I don't think are relevent to this query. I'm using a recent MS SQL-Server database.

There is even a separate tag for such questions - greatest-n-per-group. — jkavalik, Nov 12 '15 at 12:07

Peter Elzinga · Accepted Answer · 2015-11-12T12:44:33.817

This should work:

SELECT      *
FROM        (SELECT         ROW_NUMBER() OVER (PARTITION BY p.Partnum 
                                               ORDER BY     p.Partnum
                                                            , pd.DueDate
                                                            , pd.Quantity) AS rownumber
                            , p.Partnum
                            , pd.Quantity
                            , pd.DueDate
                FROM        Part       AS p
                INNER JOIN  PartDtl    AS pd
                    ON      p.Company  = pd.Company
                        AND p.PartNum  = pd.PartNum
            ) AS sub
WHERE       sub.rownumber <= 3
ORDER BY    sub.Partnum
            , sub.DueDate
            , sub.Quantity

You were using old SQL syntax, I converted the old join type to an INNER JOIN. Also changed the numbers in the ORDER BY clause, as this might cause problems when you change the order of your selected columns.

That did work, thank you. A few things in that solution I've not seen before. — Michael Vincent, Nov 12 '15 at 14:17

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

If you are using the middle the query to get the data you could try a CTE, something like this:

with greatest_n_per_group as
(
    select 
      p.PartNum, 
      pd.Quantity,
      pd.DueDate,
      first_few = row_number() over (partition by p.PartNum order by pd.DueDate asc, pd.Quantity asc)
    from 
      Erp.Part as p, 
      Erp.PartDtl pd
    where
      p.Company = pd.Company and 
      p.PartNum = pd.PartNum and 
      pd.StockTrans = 'TRUE'
)
select * from greatest_n_per_group
where first_few <= 3
order by PartNum asc, first_few asc;

There is a couple of questions already asked that have the answer for this like this one: How to select the first row of each group?

You can get the syntax from them and just modify as and if you need to.

Limiting rows from a joined table

2 Answers2

Linked