Geek Answers Handbook

How to select all records from n groups?

I want to select entries from the first n groups. My data is as follows:

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 device identifier (id) is unique within the group, but not unique in its column, because the group instance is generated regularly. A group identifier (gid) is assigned to each module, but is not generated in more than one instance.

Now combining tables and selecting everything or a filter by a specific value is easy, but how can I select all the records, for example, the first two groups without directly accessing the group identifiers? Expected Result:

  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

Additional question: when I filter for a timeframe, for example:

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

Is there an easy way to ensure that groups are not cut off, but are either displayed with all their records or not at all?

+6
source share
5 answers

You can use ROW_NUMBER() for 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 view to get your other information and use the where clause to filter out the number of groups you want to see. For example, the top 5 RN <= 5 groups RN <= 5 will be returned below:

 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 date question, you can do this with a subquery, for example:

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

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 it will get the full first group.

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

+1
source

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

demo works here

+1
source

DENSE_RANK looks like the perfect solution here

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

No need to use rank 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;

Same thing 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;
0
source

More articles:


All Articles