serial no

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » serial no

14 posts, Page 1 of 2

1 2 »»

serial no

Rate Topic

Display Mode

Topic Options

Author

Message

znkin

Posted Friday, March 20, 2009 11:21 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, September 22, 2012 12:31 AM
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

Post #680598

Florian Reischl

Posted Friday, March 20, 2009 11:28 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

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

Post #680604

Lowell

Posted Friday, March 20, 2009 11:51 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 11,626, Visits: 27,686

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #680615

Jeff Moden

Posted Friday, March 20, 2009 5:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 32,905, Visits: 26,787

 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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #680802

znkin

Posted Friday, March 20, 2009 8:43 PM

Valued Member

Group: General Forum Members
Last Login: Saturday, September 22, 2012 12:31 AM
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

Post #680837

Jeff Moden

Posted Friday, March 20, 2009 9:02 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 32,905, Visits: 26,787

Then the query I included with my post should do it.

--Jeff Moden

Post #680841

znkin

Posted Saturday, March 21, 2009 12:35 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, September 22, 2012 12:31 AM
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.

Post #680861

Florian Reischl

Posted Saturday, March 21, 2009 2:32 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Hi

Which version of SQL Server are you using?

Greets
Flo

Post #680877

znkin

Posted Saturday, March 21, 2009 2:51 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, September 22, 2012 12:31 AM
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

Post #680882

andrewd.smith

Posted Saturday, March 21, 2009 3:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232

SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo, 
CustomerId,CustomerName FROM dbo.CustMaster

Post #680891

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions