Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

serial no Expand / Collapse
Author
Message
Posted Friday, March 20, 2009 11:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, March 20, 2009 11:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, 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
Posted Friday, March 20, 2009 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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
Posted Friday, March 20, 2009 5:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #680802
Posted Friday, March 20, 2009 8:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, March 20, 2009 9:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #680841
Posted Saturday, March 21, 2009 12:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Saturday, March 21, 2009 2:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #680877
Posted Saturday, March 21, 2009 2:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Saturday, March 21, 2009 3:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,212, Visits: 3,232
SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo, 
CustomerId,CustomerName FROM dbo.CustMaster

Post #680891
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse