CODEWITHSUNDEEP
sqlsql-servergroup-by
user3417479
user3417479

Reputation: 1910

Select rows so total number not to exceed a certain number

I am using SQL Server 2008 and I have a SALES_ORDER table. Each row of the table is one material. Many rows (materials) can belong to the same ORDER. I want to select the materials of many orders under two conditions:

  1. All materials under the same order will be selected (orders will not be split).
  2. The total number of selected rows will not exceed a maximum predefined number ROW_MAX.

For example, let's say we have

4 materials under order_1
3 materials under order_2
5 materials under order_3
2 materials under order_4

and ROW_MAX = 7

the query must return

Any idea how to do it with SQL script?

Upvotes: 0

Views: 547

Answers (2)

davidc2p
davidc2p

Reputation: 320

I believe it's a bit more tricky than that. I guess the following code answers the requisite:

any other combination of orders so the total number or selected rows does not exceed 7

There's little work to do before you can cross apply.

First we create a data sample in which order1 + order2 is bigger than 7 (so that we can test and doesn't stops looking after order2):

-- A sample table in which order1 + order 2 count is bigger than 7
IF OBJECT_ID('tempdb..#sample') is not null
    DROP TABLE #sample
CREATE TABLE #sample (
    OrderID     INT,
    OrderDesc   VARCHAR(50)
)

INSERT INTO #sample VALUES 
(1, 'Order 1 customer 1'),
(1, 'Order 2 customer 1'),
(1, 'Order 3 customer 1'),
(1, 'Order 4 customer 1'),
(2, 'Order 1 customer 2'),
(2, 'Order 2 customer 2'),
(2, 'Order 3 customer 2'),
(2, 'Order 4 customer 2'),
(2, 'Order 5 customer 2'),
(3, 'Order 1 customer 3'),
(3, 'Order 2 customer 3'),
(4, 'Order 1 customer 4')

Then we count the number of row for each order:

-- Counting rows for each order
IF OBJECT_ID('tempdb..#samplecount') is not null
   DROP TABLE #samplecount
SELECT OrderID, COUNT(*) as OrderCount
INTO #samplecount
FROM #sample
GROUP BY OrderID

We order the orders according that count:

--Numbering each row
IF OBJECT_ID('tempdb..#samplecountordered') is not null
   DROP TABLE #samplecountordered
SELECT *, ROW_NUMBER() OVER(ORDER BY OrderCount) AS OrderNumber
INTO #samplecountordered
from #samplecount

After that we can use cross apply on that organised repository:

select sa.*
from #samplecountordered so cross apply
     (select SUM(OrderCount) as running_count
      from #samplecountordered so2
      where so2.OrderNumber <= so.OrderNumber
      ) so2
INNER JOIN #sample sa
ON sa.OrderID = so.orderID    
where running_count <= 7;

This solution requires further testing, but I guess it is the spirit. I use temporary tables so that it is easier to follow in terms of construction.

Sorry, I used customers instead of material.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are looking for a cumulative sum. In SQL Server 2008, you can do this with apply:

select so.*
from sales_order so cross apply
     (select count(*) as running_count
      from sales_order so2
      where so2.order_id <= so.order_id
     ) so2
where running_count <= 7;

Upvotes: 0

Related Questions