serial no

  • 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

  • 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

  • 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!

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

    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)

  • 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

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

    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)

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

  • Hi

    Which version of SQL Server are you using?

    Greets

    Flo

  • 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

  • SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo,

    CustomerId,CustomerName FROM dbo.CustMaster

  • that error could occur if your database compatibility level is at 80 instead of 90;

    the compatibility level determines which syntax version is applied to statements hitting the database, and rownnumber() is a 90 and above functionality.

    change the version and you should be all set.

    znkin (3/21/2009)


    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.

    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!

  • I don't think it's the compatibility level...

    Looks more like a typo: ROWNUMBER vs. ROW_NUMBER()

    Underscores sometimes matter 😛

    The RowNumber function is for Reporting Services.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Dangit... sorry folks... I posted in a hurry and left out the underscore in ROW_NUMBER(). Andrew has the correct code and I went back to my previous erroneous post and fixed that, as well.

    --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)

  • I want devide this table in to groups like in first block display Id(1-5) and second block desplay data (5-10) and onward plz help

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

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