How can I give specific number by group of identical values in a select clause?-CodePudding

I have this :

Person	Dinner
Paul	Apple
Alfred	Banana
John	Apple
Jimmy	Banana
Johnny	Strawberry

I want to give a specific number for each distinct value in the Dinner column in a select clause like this :

Person	Dinner	Group
Paul	Apple	1
Alfred	Banana	2
John	Apple	1
Jimmy	Banana	2
Johnny	Strawberry	3

I tried this get the groups :

SELECT case when (lag(Dinner) OVER (ORDER BY id) = Dinner) or (lead(Dinner) OVER (ORDER BY Dinner) = Dinner) then 1 else 0 end,* FROM restaurant ORDER BY Dinner desc)

And it gives me this :

Person	Dinner	Group
Paul	Apple	1
John	Apple	1
Alfred	Banana	0
Jimmy	Banana	0
Johnny	Strawberry	1

It only gives me 1 or 0 because of the case when case.

I'm pretty sure there's easier way to do it, but I didn't find any.

Any help ?

CodePudding user response：

Why not use DENSE_RANK?

SELECT Person,
       Dinner,
       DENSE_RANK() OVER (ORDER BY Dinner ASC) AS [Group]
FROM (VALUES('Paul','Apple'),
            ('Alfred','Banana'),
            ('John','Apple'),
            ('Jimmy','Banana'),
            ('Johnny','Strawberry'))V(Person, Dinner);