August 6, 2002 at 11:56 am
Is there anyway to create a serial row number in a select statement? i.e.
row 1 Mary Smith 123 Apple St. .....
row 2 Tommy Jones 342 Maple St. ....
Maybe something similar to RowID as in Oracle?
Greg H
Greg H
August 6, 2002 at 12:29 pm
No easy way. I've seen some complicated SQL (using count() and subqueries), but nothing I'd recommend. Any reason not to do it on the client?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 6, 2002 at 1:23 pm
What will you be using the rowid for?
Andy
August 6, 2002 at 1:43 pm
Hi,
In this case, we were looking for a way to insert an item line count in a way that wouldn't interfere with our primary sort criteria: postal codes. For instance, on the client side, when we run a filter on the resulting select set( invoices ) based on say line 523, it dumped lines 1-522 to the bottom of the stack and reset the line numbers at 1 starting with what was row 523. Not good for USPS costs. We hope this is a one time only situation involving a small run(<1,000 rows). We can manually scroll through the list if need be.
Greg H
Greg H
August 7, 2002 at 12:57 am
ok !! first of all i suggest you use a stored procedure to return your result set
however there is a way to return a serial number with a resultset , but only if the table has a unique key
CREATE TABLE #val
(
val1 int primary key,
val2 char(1)
)
INSERT INTO #Val
values(1, 'Z')
INSERT INTO #Val
values(3, 'D')
INSERT INTO #Val
values(8, 'T')
INSERT INTO #Val
values(5, 'B')
INSERT INTO #Val
values(11, 'A')
INSERT INTO #Val
values(9, 'D')
select t2.val1, t2.val2,
(select count(*) from #val t1 where (t1.val1 <= t2.val1) and (t1.val2 <= t2.Val2)) AS SrNo
from #val t2
order by Srno
--this would return a rowid based on the primarykey
select t2.val1, t2.val2,
(select count(*) from #val t1 where ( (t1.val2 + CONVERT(varchar(10),t1.val1) ) <= ( t2.val2 + CONVERT(varchar(10),t2.val1)) ) ) AS SrNo
from #val t2
order by SrNo
drop table #val
--if we take the val2 column as your postcode , we can return a rowid based on
--the sorted rows using a composite column of the sort field and the primary key
however i don't think this approach is the best way , a procedure which takes the resultset into a temp table with a identity key is probably the best way to go
October 10, 2002 at 12:40 pm
You can generate a serial OR sequence number in a SELECT statement using the IDENTITY function ( do not mistake it with the table property: INDENTITY although similar functionality ).
The only requirement is that the command must include the INTO clause and the inserted table should not exist already in the schema.
Here is an example: Suppose you need to generate a serial number starting from 100 ( incremented by 1 ) with a record and insert it into a temporary table.
SELECT IDENTITY(smallint, 100, 1) AS job_num,
col1, col2, col3, col4
into #remove_tbl
from My_table where col5='ABCD'
-- Chandra Cheedella
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply