Adding Sequence numbers to ordered rowset

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)

Adding Sequence numbers to ordered rowset

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-16 : 14:59:03

Help Mr. Crossjoin !

I've got a complex SELECT that generates a rowset that fills out a datagrid.

I know, sequencing the rows is presentation side but..

If I have a sequence table, can I use it to add a new first column to the rowset without changing the order of the rowset?

I'd like to show an abbreviated example of the problem. It's not easy.

SELECT a, b, c FROM MyTable WHERE ..... ORDER BY ....

--- Can this be modified to join with table Sequence

Select Seq, A.*
FROM Sequence S
Abracadabra (
SELECT TOP 100 percent a, b, c FROM MyTable WHERE .... ORDER BY ...
) A

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 15:26:18

Does the following state the problem correctly?

You have a sequence table:
1
2
3
5
8
...

and a data table:

a
b
c
d
e

and you'd like to see:
1 a
2 b
3 c
5 d
8 e

with the join occurring on the same ordinal row position?

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-16 : 15:52:20

Close, but importantly (I think) the data table is a subquery, complete with WHERE and ORDER BY clauses.

The trick is adding a sequence ID to the resulting subquery, preserving the order.

I've got a primitive ASP datagrid that should do this job, but it doesn't. I'd like the result set to have the column prefix so viewers of the datagrid result can identify rows by row number.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 16:28:05

Sam,

What about managing it in the backend...something like


DECLARE @data Table (col1 int IDENTITY(1,1), col2 varchar(20))


INSERT INTO @Data(col2)
SELECT 'Apples' 
UNION ALL
SELECT 'Bananas' 
UNION ALL
SELECT 'Cherries' 
UNION ALL
SELECT 'Dill' 


SELECT *, 1 AS RequestId INTO RequestTable From @Data

SELECT * From RequestTable

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-16 : 16:57:32

My choice is to insert it into a temporary table with a sequence column. Update the sequence column sequentially after the insert. Then select the whole enchelada.

I'd rather find an abracadabra join to do the job however. Nuts.

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 17:02:55

Well, you could include a rank column in the subquery and an identity in the sequence table and join on that, but you would have to work out the ties.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-16 : 17:31:04

Here's our finalist. Not too bad actually, nice curves, no tally table.

SELECT IDENTITY(1,1) as Row, A.*
INTO #MyTable
FROM (
SELECT TOP 100 percent a, b, c FROM MyTable WHERE .... ORDER BY
) A

SELECT * FROM #MyTable -- Creates the returned ROWSET
ORDER BY Row -- Is an ORDER BY necessary ?

DROP TABLE #MyTable -- Clean up

RETURN

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-16 : 17:46:15

Sam -- in this case, I think what you've posted with the temp table will be the most efficient .... too bad IDENTITY() can't be used in a SELECT statement, though I suppose I can understand why they don't allow it ....

A sequence/tally/numbers table really provides no help for this type of problem. (nor does a CROSS JOIN!)

i initially read your problem the same as Jonathan did, but it seems much simplier, so go with the temp table.

- Jeff

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-16 : 17:47:11

Umm,

I am not a sql x pert
but I created a user defined function to do the lifting.

I doubt this helps at all, but I thought I would share since I plucked
the info from here. Its probably inefficient, but seems to work pretty well.

CREATE FUNCTION [dbo].[chargeSeqNum]
(
@id int
)
RETURNS int
AS
BEGIN
Declare @seq int
Set @seq = (
Select count(*) from
(
select o.rid
from offenses o
Cross JOIN
offenses r

WHERE o.rid = r.rid
and
o.dateadded >= r.dateadded
and
o.offenseid = @offenseid
group by o.rid,r.dateadded
) as x
)
Return @seq
END

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-16 : 18:08:54

Hi Vivaldi,

That UDF probably works fine, but I'm thinking it'll be slower because it has to do a select / join to calculate a row number for every row.

What do you think?

Sam

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-17 : 10:16:26

Probably..

I don't have enough experience to really understand performance
issues at this level, outside of using logic. (I am sure you are right about it being slow)

I wasn't suggesting you use it, just showing what I did for a large view that I wrote
to hide some of the complexity.

I just don't care if the third party needs to wait for an extra second or two for the data set.

I probably should, but I don't.

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Select Seq, A.*FROM Sequence SAbracadabra (SELECT TOP 100 percent a, b, c FROM MyTable WHERE .... ORDER BY ...) A

Subscribe to SQLTeam.com

Resources

Select Seq, A.*
FROM Sequence S
Abracadabra (
SELECT TOP 100 percent a, b, c FROM MyTable WHERE .... ORDER BY ...
) A