SELECT
IDENTITY(int, 1,1) AS RowNumber
, [title]
FROM
#temp

select	row_no = (select count(*) from #temp x where x.title <= t.title),
	title
from	#temp t

quote:

---

Originally posted by Lopaka

Is there a way to query a table and have the select do a row count automatically.

create table #temp(title nchar(1))

insert into #temp(title)
select 'a' union
select 'b' union
select 'c' union
select 'd' union

select ???, title
from #temp

--output
1 a
2 b
3 c
4 d

Thank you...:)

Robert R. Barnes

---

quote:

---

the SELECT IDENTITY required SELECT INTO . . . syntax to work

---

-- Drop the #temp table if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE #temp (
	[title] CHAR(1)
	)

INSERT INTO #temp
      SELECT 'a'
UNION SELECT 'b'
UNION SELECT 'c'
UNION SELECT 'd'

-- Create a store table to keep track of row numbers
DECLARE @store TABLE (
	[RowNumber] INT IDENTITY(1,1)
	, [value] CHAR(1)
	)

-- Insert into the @store and generate the row numbers
INSERT INTO @store
SELECT
	[title]
FROM
	#temp
ORDER BY
	-- INSERT YOUR ORDER BY HERE
	[title] ASC

--Display the results
SELECT * FROM @store

quote:

---

the best method of doing this is ofcourse Madhivanan's way

---