Post reply

A column for serial number

  • July 3, 2007 at 2:43 am

    #173430

    Greetings,

    I'm sorry to ask this simple question. I want to create a column that shows the seria number of each record.

    What is the query for doing this. It should be on the left side of the Fullname column.

    Thanks and respect for you all.

  • July 3, 2007 at 3:32 am

    #716804

    You can use the indentity function in select query but that has been restricted saying that you need to include the into clause too.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 3, 2007 at 5:28 am

    #716832

    Hi

    You can use ROW_NUMBER() function for this.

    ex:- say you have a employee table select Fullname,ROW_NUMBER()

    over (order by EmployeeID) From Employees.

    "Keep Trying"

  • July 3, 2007 at 5:45 am

    #716838

    Not in SQL Server 2000, you can't... this IS an SQL Server 2000 forum, ya know?

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

  • July 3, 2007 at 6:08 am

    #716843

    ROW_NUMBER() function is available only with SQL 2005 and not with 2000.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 3, 2007 at 6:14 am

    #716846

    Must be an echo, eh?

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

  • July 4, 2007 at 4:50 am

    #717118

    Oops!!

    Sorry for the incorrect post.

    "Keep Trying"

  • July 4, 2007 at 2:01 pm

    #717236

    Thank you all for your quick respone and trying to help me.

    I use sql 2005 but I couldn't use the (row_number function) but no problem, I want only to thank you.

     

  • July 5, 2007 at 8:31 am

    #717401

    Thank you for the feedback...

    If you are still having problems or need to use "row_number" (now that we know you are using 2k5) instead of an Identity column, please post back...

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

  • July 7, 2007 at 4:50 am

    #717834

    Greetings Jeff,

    Thanks for your care. I'm really eager to know how to use it.

    Here is a copy of my main query,

     

    SELECT FullName, JobCode, City, HomePhone, Mobile, Notes, NextInterview, eMailAddress, Experience, EmployeeID, NeedHim, Interview2, MeetDr1, MeetDr2, Time1, Time2, Arrival1, Arrival2, Result1, Result2 FROM EmployeeOrders WHERE (NeedHim = 1) ORDER BY FullName

    Thanks.

  • July 10, 2007 at 7:31 am

    #718291

    Here you go (sorry for the lousy format)

     

     

    SELECT ROW_NUMBER() OVER (ORDER BY FullName) AS SerialNumber, FullName, JobCode, City, HomePhone, Mobile, Notes, NextInterview, eMailAddress, Experience, EmployeeID, NeedHim, Interview2, MeetDr1, MeetDr2, Time1, Time2, Arrival1, Arrival2, Result1, Result2 FROM EmployeeOrders WHERE (NeedHim = 1) ORDER BY FullName

     

    Best regards!

  • July 10, 2007 at 9:26 am

    #718331

    Hi Aaron,

    Thank you very much for your time and patience.

    I copied the query and tried several methods but I failed.

    By the way, I use SQL 2005 and Visual Studio Pro 2005.

    I receive an error message that (The over sql construct or statement is not supported).

    Thanks again.

  • July 13, 2007 at 2:54 pm

    #719424

    sounds like even though your database is sql2005, the database compatibility is set to 80 and not 90, so you are still limited to SQL2000 based solutions.

     

     

    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!

  • July 14, 2007 at 2:58 am

    #719478

    Hi there,

    At last, I found the solution. Here it is.

    EX:

    Select ROW_NUMBER() over (order by firstname) as ROWNUMB, firstname, lastname, employeeID from employee

    Here, when you try to excute the query, you will get an error message that says " The OVER sql construct or statement is not supported".

    Press ignore and compltete your work. Make sure that a column named RowNumber or serial is added to the table or you add it manually.

    Save the changes on the table.

    You will get the expected result, and everything will be OK.

    Thanks for everyone who tried to help me.

     

    I got this solution from Microsoft forums. I tried it successfully.

  • July 14, 2007 at 7:10 am

    #719481

    Gotta love that... a GUI based tool that doesn't support the base product... kinda like EnterPrise Manager with Case statements...

     

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

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

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