SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


serial no


serial no

Author
Message
znkin
znkin
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 99
How do I generate a serial no. within a select query
My query is

Select CustomerId,CustomerName from CustMaster

and I want the output as
1 23232 John
2 32323 Amit
3 82321 KY


Also another question is

I have a variable as type datetime like
declare @PFDate datetime
set @PFDate = '2009-03-19 00:00:00.000'
and I need to add time to this datetime variable. Like if I would add
15 hours 30 min then the variable should become '2009-03-19 15:30:00.000'

Thanks
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3471 Visits: 3934
For the first question:
What is the specification for the Serial No? Any incremental ID? Should it contain any information? Should it be a NVE or a Code39?

For the second question:

declare @PFDate datetime
set @PFDate = '2009-03-19 00:00:00.000'

select @PFDate, @PFDate + CONVERT(DATETIME, '15:30')



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28103 Visits: 39938
My questions are the same:
what is a serial number to you? is it a random number? can it be '0000' plust the customer number?(0001,0002, etc). Why did your example have a wide range of values?
is the number unique? does it need to follow a business pattern(ie SN0000YYYY0001 so it makes "at-a-glance' sense to some people?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
 SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo,
CustomerId,
CustomerName
FROM dbo.CustMaster



But, I agree with the others... what are you going to use it for? The answer to that question could make a huge difference.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
znkin
znkin
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 99
Thanks for the replies. I am looking for a simple serial no. count starting with 1,2,3......
I just have to display the customerid and customername along with a serial no. starting from 1 , so I was looking to generate this serial no. in the sql query only.

sample output if the query returned 10 records

SR No. CustomerId Customername
1 4444444 ABC
2 3332323 XYX
3 3232323 YTR
'
'
'
10 4343434 YTTT
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
Then the query I included with my post should do it.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
znkin
znkin
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 99
I tried your example with sql 2005 but it gives me this error

Msg 195, Level 15, State 10, Line 1
'ROWNUMBER' is not a recognized built-in function name.
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3471 Visits: 3934
Hi

Which version of SQL Server are you using?

Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
znkin
znkin
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 99
I am using SQL server 2005 and when I try this code for generating a running sequence no. to start from 1 it gives me the error

SELECT ROWNUMBER() OVER (ORDER BY CustomerID) AS SerialNo,
CustomerId,CustomerName FROM dbo.CustMaster
andrewd.smith
andrewd.smith
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 3232
SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo, 
CustomerId,CustomerName FROM dbo.CustMaster


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search