How to add serial number to a resultset

Question

How to add serial number to a resultset

Raghavendra-499237

Default port

Points: 1435
More actions
July 8, 2008 at 3:57 am

#71629

Hi all,
I have a table which stores batch details.I have written a query which retrieves batches associated with multiple transactions (which is another table to store Transaction details). My resultset is something like this
BatchIdTransId
1011
1012
1013
1024
1025
1036
Now I want to add serial Number to my resultset, and my resultset should come like this.
SlNo.BatchIdTransId
11011
11012
11013
21024
21025
31036
thanks in advance

Viewing 15 posts - 1 through 15 (of 15 total)

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

create either a temp table or a table variable to store the serial numbers and then use a SELECT statement to generate the serial numbers -- something like:

declare @serialNo table

( BatchID int not null primary key,

slNo int identity

)

insert into @serialNo

select distinct batchId

from yourSourceTable

order by batchId

Then JOIN the @serialNo table into your select table by the "BatchID" column and thereby gain access to the slNo column from your temp table.

Gougou SSC Rookie Points: 38 More actions · Answer 2

Please run this query:

select SubString(BatchId,3,1) as SlNo,BatchId,TransId Into NewTable

from OldTable

order by BatchId

I suppose you have varchar data type for BatchId.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Kent Waldrop (7/8/2008)
create either a temp table or a table variable to store the serial numbers and then use a SELECT statement to generate the serial numbers -- something like:
declare @serialNo table
( BatchID int not null primary key,
slNo int identity
)
insert into @serialNo
select distinct batchId
from yourSourceTable
order by batchId
Then JOIN the @serialNo table into your select table by the "BatchID" column and thereby gain access to the slNo column from your temp table.

I don't think so... this is a RANK problem...

See the following, please...

http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/

--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)
Intro to Tally Tables and Functions

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 4

Kent Waldrop

Hall of Fame

Points: 3064

July 9, 2008 at 7:18 am

#840101

Thank you for picking me up, Jeff.

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

Nah... not a problem Kent... I just had more coffee than you 😛

--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)
Intro to Tally Tables and Functions

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

Try this:

DECLARE @Table TABLE

( BatchID INT

,TransactionID INT

)

INSERT INTO @Table

SELECT 101, 1

UNION ALL SELECT 101, 2

UNION ALL SELECT 101, 3

UNION ALL SELECT 102, 4

UNION ALL SELECT 102, 5

UNION ALL SELECT 103, 6

SELECT BatchID

,TransactionID

,Dense_Rank() OVER (ORDER BY BatchID) AS SerialNumber

FROM @Table

ORDER BY BatchID

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 7

Yes, ranking functions might work; however, I assumed that since this was a SQL Server 7, 2000 forum that an SQL 2005 only option would not be viable. Is this question intended for SQL Server 2000?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

ggraber (7/9/2008)
Try this:
DECLARE @Table TABLE
( BatchID INT
,TransactionID INT
)
INSERT INTO @Table
SELECT 101, 1
UNION ALL SELECT 101, 2
UNION ALL SELECT 101, 3
UNION ALL SELECT 102, 4
UNION ALL SELECT 102, 5
UNION ALL SELECT 103, 6
SELECT BatchID
,TransactionID
,Dense_Rank() OVER (ORDER BY BatchID) AS SerialNumber
FROM @Table
ORDER BY BatchID

This is an SQL Server 2000 forum... RANK does not exist and an alternate method must be found. Please see the following link for that...

http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/

--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)
Intro to Tally Tables and Functions

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 9

Sorry, I missed that. :blush:

Will this work?

DECLARE @Table TABLE

( BatchID INT

,TransactionID INT

)

INSERT INTO @Table

SELECT 101, 1

UNION ALL SELECT 101, 2

UNION ALL SELECT 101, 3

UNION ALL SELECT 102, 4

UNION ALL SELECT 102, 5

UNION ALL SELECT 103, 6

DECLARE @Table2 TABLE

(

BatchID INT

,TransactionID INT

,SerialNumber INT

)

INSERT INTO @Table2 (BatchID, TransactionID)

SELECT * FROM @Table

DECLARE @Counter INT

DECLARE @LastBatchID INT

SET @Counter = 0

SET @LastBatchID = 0

UPDATE @Table2

SET @Counter = SerialNumber = CASE WHEN BatchID = @LastBatchID THEN @Counter

ELSE @Counter + 1

END

,@LastBatchID = BatchID

SELECT * FROM @Table2

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

I haven't executed it (the last part of the code), but it looks correct. The first part of the code still has a "Dense_RANK" in it... 2k has no RANK, Dense_Rank, or RowNUM functionality...

--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)
Intro to Tally Tables and Functions

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 11

Jeff Moden (7/9/2008)
I haven't executed it (the last part of the code), but it looks correct. The first part of the code still has a "Dense_RANK" in it... 2k has no RANK, Dense_Rank, or RowNUM functionality...

Yes, I do know that.

I just left the code there to check myself.

I should have removed it before I posted.

munireddybn-687270 Old Hand Points: 363 More actions · Answer 12

Hi all,

I agree this is a problem related to Rank in SQL server 2005. But this can still be achieved with SQL Server 2000 also in a single query given as below without using temporary Tables or Table variables.

select (select count(*) from batch c where c.batchid <= d.batchid) as SlNo, d.batchid,d.transid from

( select a.batchid,b.transid from batch a, trans b where a.batchid=b.batchid ) d

To see this in action, try the following content in query analyzer.

-- create two tables as stated in Raghavendra's the problem.

create table batch (batchid int )

create table trans (transid int,

batchid int)

-- Insert values into batch table

insert into batch values(101)

insert into batch values(102)

insert into batch values(103)

-- Insert values into trans table

insert into trans values(1,101)

insert into trans values(2,101)

insert into trans values(3,101)

insert into trans values(4,102)

insert into trans values(5,102)

insert into trans values(6,103)

-- Finally execute this query to get the desired resultset

select (select count(*) from batch c where c.batchid <= d.batchid) as SlNo, d.batchid,d.transid from

( select a.batchid,b.transid from batch a, trans b where a.batchid=b.batchid ) d

Same tactics can be used in variety of situations to get SlNo with minimal alterations.

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

That constitutes a "Triangular Join" in a correlated subquery... please read the following as to why those can be so very bad...

http://qa.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)
Intro to Tally Tables and Functions

abu_arqam Valued Member Points: 56 More actions · Answer 14

You can use

SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo,ColumnName1,ColumnName2,ColumnName3 from Tablename

Thanks