Sequence number with Group By

Question

Post reply

Sequence number with Group By

Petexley

Grasshopper

Points: 18
More actions
February 29, 2008 at 6:35 am

#187166

First time caller here
I have a simple stored procedure to group data
SELECT VatCode FROM Partner GROUP BY VatCode
which gives
STANDARD
VAT FREE
SPECIAL RATE
This works fine but I need to add a sequential number on to each result row like
1 STANDARD
2 VAT FREE
3 SPECIAL RATE
Any help would be appreciated

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

SELECT ROW_NUMBER () OVER (ORDER BY VatCode),

VatCode

FROM Partner

GROUP BY VatCode

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

SELECT

ROW_NUMBER() OVER(ORDER BY VatCode) AS [ROW],

VatCode

FROM Partner

GROUP BY VatCode

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

Beat me too it 😎

My blog: http://jahaines.blogspot.com

Petexley Grasshopper Points: 18 More actions · Answer 4

Petexley

Grasshopper

Points: 18

February 29, 2008 at 7:04 am

#783731

Crikey chaps, I am so impressed

Sorted

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

Yeah, but nothing like instant confirmation that you're right, huh? Thanks, Adam...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tosscrosby SSCoach Points: 18045 More actions · Answer 6

Anything comparable in SQL2000??

-- You can't be late until you show up.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

No. Probably the easiest way is to create a temp table with an identity column, insert your rows into that, then select from it.

John

Adam Haines SSC-Insane Points: 23217 More actions · Answer 8

The row_number function was introduced in SQL 2005. As John said, the best/easiet way to accomplish this is using temp tables.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

John Mitchell (2/29/2008)
No. Probably the easiest way is to create a temp table with an identity column, insert your rows into that, then select from it.
John

I'll third that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Of course, you could go old-school with a correlated sub-query. Celko would be so proud of me…

select

RowNumber =

(

select

Cnt = count(distinct b.VatCode)+1

from

Partner b

where

a.VatCode > b.VatCode

),

a.VatCode

from

Partner a

group by

a.VatCode

order by

a.VatCode

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

Heh... yeah, I'll just bet he would, Micheal...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

I was just kidding about the correlated sub-query; I wouldn't use it.

I locked horns with Celko on this very issue about 12 years ago on the old Compuserve SQL Server formula. I suggested to someone that they use the identity method, and Celko complained that it wasn’t ANSI SQL. I pointed out that we were on a SQL Server forum, not an ANSI SQL forum, and that was enough to get me the full treatment.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 13

Heh... I know... not a problem, Michael... just having a bit of fun 'cause you used the "C" word 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)