Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 8 down vote accepted
SELECT InvNr, DetailLine 
FROM tbl_Invoice 
where InvNr in (select distinct  top (2) InvNr from tbl_Invoice order by InvNr);
share|improve this answer
    
Much simpler that what I've wrote. – Michael Cherevko Mar 10 at 9:38

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 ;
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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