Allways 3 Rows

Transact-SQL
1

Hi,

I aggregiere data from a table, and should get the max 3 Sales Amount.
If the Customer has only 2 sales, is a DummyRow be issued with value 0; Can I join with a dummy table (Values 1,2,3) ??

No. CustID SalesAmount

1 100794 297.00
2 100794 99.00
3 100794 101.00

1 100813 124.00
2 100813 110.00
=> here there must be a Dummy Row like '3 100813 0'

Thanks
Regards
Nicole

2

hi,

you can use union with TOP 3 operator.

;with result as
(select top 3 col1 from tbl1
union
select top 3 col1 from dummytbl)
select top 3 * from result

thanks
subha

3 
SELECT row_num AS [No.], CustID, SalesAmount
FROM (
    SELECT CustID, SalesAmount, ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY SalesAmount DESC) AS row_num
    FROM (
        SELECT CustID, SalesAmount
        FROM table_name
        UNION ALL
        SELECT DISTINCT CustID, 0
        FROM table_name
        CROSS APPLY (
            SELECT 1 AS repeater UNION ALL
            SELECT 2
        ) AS repeaters
    ) AS derived
) AS derived2
WHERE row_num BETWEEN 1 AND 3
ORDER BY CustID, row_num