SQL Server Join – Limiting Rows from a Joined Table

greatest-n-per-groupsql server

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.

Best Answer

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.

Related Solutions

Sql-server – Union of Columns

If I understand you correctly you have four different queries and you want the result from two of them on the first row and the other two on the second row.

You can do sub-queries something like this:

select 'Total Pre Orders Web',
       (select 10 /*Replace this with your query*/), 
       'Total Orders Web',
       (select 20 /*Replace this with your query*/)
union all
select 'Total Pre Orders Phone',
       (select 2 /*Replace this with your query*/),
       'Total Orders Phone',
       (select 0 /*Replace this with your query*/)

Result:

---------------------- ----------- ------------------ -----------
Total Pre Orders Web   10          Total Orders Web   20
Total Pre Orders Phone 2           Total Orders Phone 0

Sql-server – Queries for unique names from a site table using multiple criteria

1 Select unique site names which have been referred to at some point.

Get rows from site where site_id exists in referrals. This assumes site name is unique in site. If not you can add a distinct.

select s.name
from dbo.site as s
where s.deleted = 0 and
      exists (
             select *
             from dbo.referrals as r
             where s.site_id = r.site_id
             )

2 Select unique site names and how many times they have been referred to.

Join site to referrals and count the number of rows grouped by site name.

select s.name, 
       count(*) as times_referred
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
where s.deleted = 0
group by s.name

3 Select unique site names and the last date they were referred to.

Add a join to calls and get the max call_date for each site name.

select s.name, 
       count(*) as times_referred,
       max(c.call_date) as last_call_date
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
  inner join dbo.calls as c
    on r.call_id = c.call_id
where s.deleted = 0 and
      c.deleted = 0
group by s.name

4 Select unique site names which have never been referred to.

Same as first query only you get the sites that does not exist in referrals.

select s.name
from dbo.site as s
where s.deleted = 0 and
      not exists (
                 select *
                 from dbo.referrals as r
                 where s.site_id = r.site_id
                 )

Best Answer

Related Solutions

Sql-server – Union of Columns

Sql-server – Queries for unique names from a site table using multiple criteria

Related Question