auto add a sequence field in slecect clause

Question

auto add a sequence field in slecect clause

flyday

SSC Rookie

Points: 34
More actions
December 8, 2003 at 8:00 pm

#162740

How to add a sequence number in select clause?
for example
select name,Sex from stuent
name sex
andy male
judy female
...
now I want to get the following result
1 andy male
2 judy female
3 ...
In a word I want to add a sequence field in my select result.
Thanks in advance
Andy
Hua@ljintl.com

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

I admit this answer doesn't directly answer your question - how to add a sequence number directly into a SELECT statement, but it does provide that result.

This may not be the cleanest method - it uses a temp table, which I usually try to avoid, and it adds a couple steps to your query.

The IDENTITY() function can be used when performing a SELECT...INTO [tablename], and it acts like the column is an identity column.

-- using a column of type int called Seq to hold the sequence number

SELECT IDENTITY(int, 1, 1) AS Seq, Name, Sex

INTO #tmpStudent

FROM Student

SELECT * FROM #tmpStudent

ORDER BY Seq

DROP TABLE #tmpStudent

GO

Hope this helps,

-Eddie