2

I have a result set of 500 rows, but this are for 100 invoice. I need only the top 10 of the last invoices.

I found something like similar: How to select top 10 records from each category

Sample data:

InvNr | DetailLine
111   |    1       
111   |    2
112   |    1
112   |    2
112   |    3
113   |    1
113   |    2
114   |    1
115   |    1
...   |    ...

What I hope to get is for example:

SELECT DISTINCT TOP 2 InvNr, DetailLine FROM tbl_Invoice

With this result:

InvNr | DetailLine
111   |    1       
111   |    2
112   |    1
112   |    2
112   |    3

Update:
So I need the last 10 (or first 2 in the example above) invoices they have created, but each invoice can have "x" amount of detail lines and I want all their detail lines (of these last 10) in the results.

9
SELECT InvNr, DetailLine 
FROM tbl_Invoice 
where InvNr in (select distinct  top (2) InvNr from tbl_Invoice order by InvNr);
| improve this answer | |
4

Edited to reflect what was actually being asked.

The following will give you all from the two highest InvNr

    DENSERANK   InvNr   DetailLine
    1           115     1
    1           115     2
    1           115     3
    1           115     4
    2           114     1
    2           114     2
    2           114     3
    2           114     4

Code to create a sample table:

    IF OBJECT_ID('tempdb..#Invoice', 'U') IS NOT NULL
        DROP TABLE #Invoice ;

    CREATE 
    TABLE   #Invoice
            (
            InvNr           INT NOT NULL
            ,DetailLine     TINYINT NOT NULL
            );

    INSERT
    INTO    #Invoice
            ( InvNr, DetailLine )

    VALUES  (111,1),       
            (111,2),
            (111,3),       
            (111,4),
            (111,5),       
            (111,6),
            (112,1),
            (112,2),
            (112,3),
            (113,1),
            (113,2),
            (113,3),
            (113,4),
            (114,1),
            (114,2),
            (114,3),
            (114,4),
            (115,1),
            (115,2),
            (115,3),
            (115,4) ;

Actual query:

WITH   CTERowNumber
AS      (
        SELECT  DENSE_RANK() OVER (ORDER BY InvNr DESC) AS DENSERANK
               ,InvNr
               ,DetailLine
        FROM    #Invoice
        )

SELECT  c.DENSERANK
       ,c.InvNr
       ,c.DetailLine
FROM    CTERowNumber c
WHERE   c.DENSERANK <= 2 ;
| improve this answer | |
2

Try this one:

WITH CTE AS  
(SELECT DENSE_RANK() OVER (ORDER BY InvNr DESC) AS InvNrRank, InvNr
FROM Your_Table)
SELECT DISTINCT YT.InvNr, YT.DetailLine
FROM Your_Table YT
JOIN CTE ON YT.InvNr = CTE.InvNr
And CTE.InvNrRank <= 10;

If you want first InvNr just replace DESC with ASC

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.