transact-sql question

Question

Hi,

Assume there were 100 records in tableA and tableA contained a column named 'price'.

How do I select the first-n record if where sum of price > a certain amount (e.g. 1000) without using cursor?

thanks

Answer 1

select price from tableA where price > 1000 limit n;

n - no. of records you want in result set

-- Cheers

Answer 2

Top N implies some kind of order, which you did not supply, so I assumed any random order.

You can change this on the OVER clause of the ROW_NUMBER().

Try something like

DECLARE @Table TABLE(
        Price FLOAT
)

INSERT INTO @Table SELECT 1
INSERT INTO @Table SELECT 11
INSERT INTO @Table SELECT 12
INSERT INTO @Table SELECT 15
INSERT INTO @Table SELECT 10
INSERT INTO @Table SELECT 65
INSERT INTO @Table SELECT 100

DECLARE @TotalPrice FLOAT
SELECT  @TotalPrice = 100

;WITH Vals AS (
        SELECT  *,
                ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RNR
        FROM    @Table
)
, Totals AS (
        SELECT  v.RNR,
                SUM(vP.Price) TotalPrice
        FROM    Vals v LEFT JOIN
                Vals vP ON  v.RNR >= vP.RNR
        GROUP BY    v.RNR
)
, LimitValue AS (
        SELECT  TOP 1 
                RNR
        FROM    Totals
        WHERE   TotalPrice >= @TotalPrice
        ORDER BY    RNR
)
SELECT  *
FROM    Vals 
WHERE   RNR <= (
                    SELECT  RNR
                    FROM    LimitValue
                )