Correct grouping sql

Question

I am attempting to group some results in a table, by their id and name. For the group, I want to also select the next_id, the lead value of the next record that is part of the group. When I issue a query like this:

SELECT id, grade, name,
    LEAD(id) OVER (ORDER BY name ASC, id ASC) as next_id
    FROM dbo.GRADES
    where name like '%alan%'
    order by name ASC, id asc;

I get this result, which is what I expect

28273   100 alan-jones  28274
28274   100 alan-jones  28275
28275   100 alan-jones  28276
28276   200 alan-jones  28280
28280   100 alan-jones  28281
28281   100 alan-jones  28282
28282   100 alan-jones  NULL

NULL specifies that the results above have no next record to point to.

However, when I run this query on more people than alan, i.e without the where clause:

SELECT id, grade, name,
    LEAD(id) OVER (ORDER BY name ASC, id ASC) as next_id
    FROM dbo.GRADES
    order by name ASC, id asc;

I get the following results, that I did not expect

28277   100 mike-cools  28278
28278   100 mike-cools  28283
28283   200 mike-cools  28284
28284   200 mike-cools  28279
28279   200 mike-cools  28273
28273   100 alan-jones  28274
28274   100 alan-jones  28275
28275   100 alan-jones  28276
28276   200 alan-jones  28280
28280   100 alan-jones  28281
28281   100 alan-jones  28282
28282   100 alan-jones  NULL

The row in particular that is causing the problem for me is:

28279   200 mike-cools  28273
28273   100 alan-jones  28274

This row should read:

28279   200 mike-cools  NULL
28273   100 alan-jones  28274

I cannot figure out as to how I can alter my query to group correctly, and not point to the next_id if the group is not the same.

score 4 · Accepted Answer · answered Oct 17 '16 at 12:34

4

You need to partition your lead call by the name, not order by it

SELECT   id, grade, name,
         LEAD(id) OVER (PARTITION BY name ORDER BY id ASC) as next_id
         -- Here -------^
FROM     dbo.GRADES
ORDER BY name ASC, id asc;

answered Oct 17 '16 at 12:34

Mureinik

297,002
52
306
350

ah, that was it – angryip Oct 17 '16 at 12:36
if I do an order by in the lead function, wouldn't I want the same order by in the last statement of the main function? – angryip Oct 17 '16 at 12:36
2

@angryip: You *can* use `PARTITION BY name ORDER BY name ASC, id ASC`, but as there is just one name per partition, it's superfluous. – Thorsten Kettner Oct 17 '16 at 12:41
@angryip yup, what Thorsten said. – Mureinik Oct 17 '16 at 12:42
1

I understand. Thanks. – angryip Oct 17 '16 at 12:44

Correct grouping sql

1 Answers1