Limiting rows from a joined table

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply