A column for serial number

Question

A column for serial number

Mohamed Saleh-392080

Grasshopper

Points: 23
More actions
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.

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

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

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"

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

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)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

ChiragNS

One Orange Chip

Points: 26137

July 4, 2007 at 4:50 am

#717118

Oops!!

Sorry for the incorrect post.

"Keep Trying"

Mohamed Saleh-392080 Grasshopper Points: 23 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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)

Mohamed Saleh-392080 Grasshopper Points: 23 More actions · Answer 9

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.

F. Dwarf SSCommitted Points: 1598 More actions · Answer 10

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!

Mohamed Saleh-392080 Grasshopper Points: 23 More actions · Answer 11

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.

Lowell SSC Guru Points: 323518 More actions · Answer 12

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!

Mohamed Saleh-392080 Grasshopper Points: 23 More actions · Answer 13

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

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)