Xen Xen - 6 days ago 6
SQL Question

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?

Answer

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