How to select distinct TOP but group by column

Question

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.

paparazzo · Accepted Answer · 2016-03-10T10:15:23.507

9

SELECT InvNr, DetailLine 
FROM tbl_Invoice 
where InvNr in (select distinct  top (2) InvNr from tbl_Invoice order by InvNr);

edited Mar 10 '16 at 10:15

answered Mar 10 '16 at 09:35

paparazzo

5,048
1
19
32

Pixelated · Answer 2 · 2016-03-10T11:37:13.340

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 ;

score 2 · Answer 3 · answered Mar 10 '16 at 09:36

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

How to select distinct TOP but group by column

3 Answers3