Displaying Row Index

[RyanEK]

Hi All,

What's the best way to display the corresponding row index when displaying return results? I need it done in one SQL, so no variables, cursors, temp tables etc. ie.

Index Fruit
--------------
1 Apple
2 Orange
3 Pear

Thanks!
Ryan

 

[mrdenny]

What version of SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[RyanEK]

Hey Denny,

Good point... I know with 2005 you can use an Identity, but I need this to work on 2000.

Thanks
Ryan

 

[mrdenny]

With SQL 2005 you actualy use ROW_NUMBER, not IDENTITY.

Based on the limits that you've set (no temp tables, cursors, etc) there really isn't a way to do this in SQL 2000.

The typicall way to do this in SQL 2000 would be to use a temp table or table variable with an IDENTITY column.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mrdenny]

I forgot to ask, why the limit of doing this in a single command?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[RyanEK]

Hey Denny,

Actually, this would do it...

select rank=count(*), a1.name
from Fruits a1, Fruits a2
where a1.name >= a2.name
group by a1.name
order by rank
[\code]

I can define the order by another field if i had to.

I'm using a reporting tool that only accepts sql commands. I could always call a stored procedure, but seems overkill for something that appears trivial.

Thanks anyways :)
Ry

 

[mrdenny]

I could always call a stored procedure, but seems overkill for something that appears trivial.

Everything should be called through a stored procedure. It's better for security and for cached execution plans, espeically in SQL 2000.

That code will work, provided that you have no duplicate values in the name column.

When I run that code against the syscolumns table in my database I get back

rank    name
16	ActivityDuration
17	addr
23	agent_type
24	algorithm
25	AlterTimeStamp
38	auid
42	backuplsn
46	bitlength
47	bitposint
48	bitposleaf
....

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog