angryip angryip - 1 month ago 5
SQL Question

Grouping while maintaining next record

I have a table (NerdsTable) with some of this data:

-------------+-----------+----------------
id name school
-------------+-----------+----------------
1 Joe ODU
2 Mike VCU
3 Ane ODU
4 Trevor VT
5 Cools VCU


When I run the following query

SELECT id, name, LEAD(id) OVER (ORDER BY id) as next_id
FROM dbo.NerdsTable where school = 'ODU';


I get these results:

[id=1,name=Joe,nextid=3]
[id=3,name=Ane,nextid=NULL]


I want to write a query that does not need the static check for

where school = 'odu'


but gives back the same results as above. In another words, I want to select all results in the database, and have them grouped correctly as if i went through individually and ran queries for:

SELECT id, name, LEAD(id) OVER (ORDER BY id) as next_id FROM dbo.NerdsTable where school = 'ODU';
SELECT id, name, LEAD(id) OVER (ORDER BY id) as next_id FROM dbo.NerdsTable where school = 'VCU';
SELECT id, name, LEAD(id) OVER (ORDER BY id) as next_id FROM dbo.NerdsTable where school = 'VT';


Here is the output I am hoping to see:

[id=1,name=Joe,nextid=3]
[id=3,name=Ane,nextid=NULL]
[id=2,name=Mike,nextid=5]
[id=5,name=Cools,nextid=NULL]
[id=4,name=Trevor,nextid=NULL]


Here is what I have tried, but am failing miserably:

SELECT id, name,
LEAD(id) OVER (ORDER BY id) as next_id
FROM dbo.NerdsTable
ORDER BY school;

-- Problem, as this does not sort by the id. I need the lowest id first for the group

SELECT id, name,
LEAD(id) OVER (ORDER BY id) as next_id
FROM dbo.NerdsTable
ORDER BY id, school;

-- Sorts by id, but the grouping is not correct, thus next_id is wrong


I then looked on the Microsoft doc site for aggregate functions, but do not see how i can use any to group my results correctly. I tried to use GROUPING_ID, as follows:

SELECT id, GROUPING_ID(name),
LEAD(id) OVER (ORDER BY id) as next_id
FROM dbo.NerdsTable
group by school;


But I get an error:

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause


Any idea as to what I am missing here?

Answer

From your desired output it looks like you are just trying to order the records by school. You can do that like this:

SELECT id, name
FROM dbo.NerdsTable
ORDER BY school ASC, id ASC

I don't know what next ID is supposed to mean.

Comments