CODEWITHSUNDEEP
sqlsql-server
Xen
Xen

Reputation: 115

How to select all records of n groups?

I want to select the records of the top n groups. My data looks like this:

Table 'runner':

    id    gid    status    rtime
    ---------------------------
    100   5550   1         2016-08-19 
    200   5550   2         2016-08-22
    300   5550   1         2016-08-30
    100   6050   3         2016-09-01
    200   6050   1         2016-09-02
    100   6250   1         2016-09-11
    200   6250   1         2016-09-15
    300   6250   3         2016-09-19

Table 'static'

    id    description   env
    -------------------------------
    100   something 1   somewhere 1
    200   something 2   somewhere 2
    300   something 3   somewhere 3

The unit id (id) is unique within the group but not unique in its column, because an instance of the group is generated regularly. The group id (gid) is assigned to every unit but will not generate on more than one instance.

Now, combining the tables and selecting everything or filter by a specific value is easy, but how do I select all records of, for example, the first two groups without directly refering to the group ids? Expected result would be:

    id    gid    description   status    rtime
    --------------------------------------
    300   6250   something 2   3         2016-09-19
    200   6250   something 1   1         2016-09-15
    100   6250   something 3   1         2016-09-11
    200   6050   something 2   1         2016-09-02
    100   6050   something 1   3         2016-09-01

Extra Question: When I filter for a timeframe like this:

    [...]
    WHERE runner.rtime BETWEEN '2016-08-25' AND '2016-09-16'

Is there a simple way of ensuring, that groups are not cut off but either appear with all their records or not at all?

Upvotes: 9

Views: 2023

Answers (5)

Vadim Ovchinnikov
Vadim Ovchinnikov

Reputation: 14012

No need to use ranking functions (ROW_NUMBER, DENSE_RANK etc).

SELECT r.id, gid, [description], [status], rtime
FROM runner r
INNER JOIN static s ON r.id = s.id
WHERE gid IN (
    SELECT TOP 2 gid FROM runner GROUP BY gid ORDER BY gid DESC
)
ORDER BY rtime DESC;

The same using CTE:

WITH grouped
AS
(
    SELECT TOP 2 gid 
    FROM runner GROUP BY gid ORDER BY gid DESC
)
SELECT r.id, grouped.gid, [description], [status], rtime
FROM runner r
INNER JOIN static s ON r.id = s.id
INNER JOIN grouped ON r.gid = grouped.gid
ORDER BY rtime DESC;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

DENSE_RANK looks like a ideal solution here

Select * From 
(
select DENSE_RANK() over (order by gid desc) as D_RN, r.*
from runner r
) A
Where D_RN = 1

Upvotes: 1

Monah
Monah

Reputation: 6784

you can do the following

with report as(
select n.id,n.gid,m.description,n.status,n.rtime, dense_rank() over(order by gid desc)  as RowNum
from @table1 n
inner join @table2 m on n.id = m.id )

select id,gid,description,status,rtime
from report
where RowNum<=2 -- <-- here n=2
order by gid desc,rtime desc

here a working demo

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

You can use a ROW_NUMBER() to do this. First, create a query to rank groups:

SELECT    gid, ROW_NUMBER() over (order by gid desc) as RN
FROM      Runner
GROUP BY  gid

Then use this as a derived table to get your other info, and use a where clause to filter to the number of groups you want to see. For instance, the below would return the top 5 groups RN <= 5:

SELECT     id, R.gid, description, status, rtime
FROM       (SELECT   gid, ROW_NUMBER() over (order by gid desc) as RN
            FROM     Runner
            GROUP BY gid) G
INNER JOIN Runner R on R.gid = G.gid
INNER JOIN Statis S on S.id = R.id
WHERE      RN <= 5 --Change this to see more or less groups

For your second question about dates, you can do this with a subquery like so:

SELECT *
FROM   Runner
WHERE  gid IN (SELECT gid 
               FROM   Runner 
               WHERE  rtime BETWEEN '2016-08-25' AND '2016-09-16')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Hmmm. I suspect this might do what you want:

select top (1) with ties r.*
from runner r
order by min(rtime) over (partition by gid), gid;

At least, this will get the complete first group.

In any case, the idea is to include gid as a key in the order by and to use top with ties.

Upvotes: 1

Related Questions