add in select number of row in all rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

add in select number of row in all rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-29 : 17:26:13

Daniele writes "Hello, this is my first question.
I'm a beginners in Sql Server, i must add a number before my columns in select.
For example i've this table:
People
first_name, last_name
MARIO ROSSI
JOHN BLACK
.... ....

when i execute a "Select * from people"
i would like obtain this:

1 MARIO ROSSI
2 JOHN BLACK

Is there a Stored Procedures o some other for my problem?

Thanks"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-01-29 : 17:52:13

Here's one way to do it:


CREATE TABLE #Temp (
Rownum int identity,
FirstName varchar(50),
LastName varchar(50)
)

INSERT INTO #Temp (FirstName,LastName)
SELECT FirstName,LastName FROM People;

SELECT * FROM #Temp ORDER BY Rownum;

DROP TABLE #Temp

butlermi_11
Starting Member

10 Posts

Posted - 2002-01-29 : 18:57:19

Here is another way to write your query using an inner join.

SELECT RANK=Count(*), P.FirstName, P.LastName
FROM People P
INNER JOIN NAMES P1
ON P.FirstName + P.LastName >= P1.FirstName + P1.LastName
GROUP BY N.FirstName, N.LastName
ORDER BY 1

butlermi_11
Starting Member

10 Posts

Posted - 2002-01-29 : 18:57:22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources