How to select all records of n groups?

Question

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?

Answer 1

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')

Answer 2

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.