Sql-server – How to select distinct TOP but group by column

group bysql serversql-server-2008-r2top

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.

Best Answer

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

Related Solutions

Sql-server – Select all data group by 1 column and functioned by another

You can use ROW_NUMBER();

WITH CTE AS
(   SELECT  ID, 
            Prioroty, 
            Type, 
            Name, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Prioroty DESC)
    FROM    T
)
SELECT  ID, Prioroty, Type, Name
FROM    CTE
WHERE   RowNum = 1;

Example on SQL Fiddle

Postgresql – Query with dynamic ranges in PostgreSQL based on status

A textbook case for window functions.
Building on a few assumptions, it could look like this:

SELECT grp
      ,count(*) AS ct
      ,sum(total) AS total
      ,min(invoice_number) AS initial_correlative
      ,max(invoice_number) AS ending_correlative
      ,status
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY id) AS grp
   FROM  (
      SELECT *, lag(status) OVER (ORDER BY id) IS DISTINCT FROM status AS step
      FROM   tbl
      ) x
   ORDER BY datetime
   ) y
GROUP  BY grp, status
ORDER  BY grp;

Subquery x marks each beginning of a new group (step).
Subquery y counts beginnings to create a unique number for each group (grp).
Then you can GROUP BY this number in outer query. status is the same for each group per definition.

SQL Fiddle.

Best Answer

Related Solutions

Sql-server – Select all data group by 1 column and functioned by another

Postgresql – Query with dynamic ranges in PostgreSQL based on status

Related Question