simple select problem

[Mike R]

Hi,

I cant figure out how to do this....

for example:
Select name from mytab order by col1

could return

Mike
Dave
Sue
Simon
Paul
Fred

I would like to show the row number, like in the grid in query analyser. so
the orginal sort order is preserved

i.e
1 Mike
2 Dave
3 Sue
4 Simon
5 Paul
6 Fred

Any ideas?

[Pachydermitis]

Mike,
Something along these lines will do it. If you are using SQL Svr 2000
use a table var instead of a temp table.
HTH
Pachydermitis

--create a temp table the will add the numbers for you
create table #whatever(NameID int not null identity(1,1), PersonsName
varchar(30))
--write the values to the table
INSERT INTO #whatever
Select name from mytab order by col1
--return the values
SELECT * FROM #whatever
--clean up
DROP TABLE #whatever

[Mike R]

"Pachydermitis" <de******@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Mike,
Something along these lines will do it. If you are using SQL Svr 2000
use a table var instead of a temp table.
HTH
Pachydermitis

--create a temp table the will add the numbers for you
create table #whatever(NameID int not null identity(1,1), PersonsName
varchar(30))
--write the values to the table
INSERT INTO #whatever
Select name from mytab order by col1
--return the values
SELECT * FROM #whatever
--clean up
DROP TABLE #whatever

Thanks for this

Kind Regards

Mike

[--CELKO--]

Since a table is **by definition** not sorted, what do you think the
"the orginal sort order" is?? Sequental files with their record
numbers behave like this, but that has nothing to do with an RDBMS.

The only way to guarantee an output in a particular order is to use an
ORDER BY clause which is part of a cursor. I would guess from this
post that you need to get a book on RDBMS foundations before you write
any code.

[Simon Hayes]

"Mike R" <ne**@mikeread.freeserve.co.uk> wrote in message
news:da*******************@news.demon.co.uk...

Hi,

I cant figure out how to do this....

for example:
Select name from mytab order by col1

could return

Mike
Dave
Sue
Simon
Paul
Fred

I would like to show the row number, like in the grid in query analyser.
so the orginal sort order is preserved

i.e
1 Mike
2 Dave
3 Sue
4 Simon
5 Paul
6 Fred

Any ideas?

http://www.aspfaq.com/show.asp?id=2427

In general, you should do this on the client side, not the server side.

Simon

[Mike R]

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Since a table is **by definition** not sorted, what do you think the
"the orginal sort order" is?? Sequental files with their record
numbers behave like this, but that has nothing to do with an RDBMS.

The only way to guarantee an output in a particular order is to use an
ORDER BY clause which is part of a cursor. I would guess from this
post that you need to get a book on RDBMS foundations before you write
any code.

I kept the posted example "very simple". I wanted to know how to get a row
number. My actual query has an order clause , it is passed to an
application that inserts another order clause which I cannot remove by any
method.

Thanks for the advice on the book, however, I dont need it.

[Mike R]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:42**********@news.bluewin.ch...

"Mike R" <ne**@mikeread.freeserve.co.uk> wrote in message
news:da*******************@news.demon.co.uk...

Hi,

I cant figure out how to do this....

for example:
Select name from mytab order by col1

could return

Mike
Dave
Sue
Simon
Paul
Fred

I would like to show the row number, like in the grid in query analyser.
so the orginal sort order is preserved

i.e
1 Mike
2 Dave
3 Sue
4 Simon
5 Paul
6 Fred

Any ideas?

http://www.aspfaq.com/show.asp?id=2427

In general, you should do this on the client side, not the server side.

Simon

Thanks Simon - great links, In the end I wrote a cursor in my SQL function
to add the identifier.

[AK]

>> Since a table is **by definition** not sorted, what do you think the
"the orginal sort order" is?? Sequental files with their record
numbers behave like this, but that has nothing to do with an RDBMS.
<<

OK, I've got a hotel with a dozen empty rooms, and a bunch of guests
arriving at once.
Using ROW_NUMBER(), available in DB2 and Oracle, I can very easily
assign a room for each guest - it would be a snap. Even though we know
that rows are not records etc, ROW_NUMBER() is a great and performant
alternative to cursors.

Having said all that, I can assign rooms to guests using pure SQL
without ROW_NUMBER, even if some guests have duplicate names. I can do
it either way no problem:

create table room(num int primary key, guest_name varchar(50))
go
insert into room(num)values(1)
insert into room(num)values(2)
insert into room(num)values(3)
insert into room(num)values(4)
insert into room(num)values(5)
go
create table guest(guest_name varchar(50))
go
insert into guest values('Brown, Jackie')
insert into guest values('Smith, Ann')
insert into guest values('Smith, Ann')
insert into guest values('Tower, Joe')
go
create table sequence_tab(n int)
go
insert into sequence_tab values(1)
insert into sequence_tab values(2)
insert into sequence_tab values(3)
insert into sequence_tab values(4)
insert into sequence_tab values(5)
go
update room set guest_name = guest_distinct.guest_name
from room,
(select guest_name, count(*) num
from guest group by guest_name) guest_distinct,
sequence_tab
where sequence_tab.n <= guest_distinct.num
and room.num = sequence_tab.n +
(select count(*) from guest
where guest.guest_name < guest_distinct.guest_name)
go
select * from room
go
drop table room
drop table guest
drop table sequence_tab

I just think the approach I've just demonstrated is a little bit too
complex, and the one with numbering rows easy to understand and
maintain:

using the same set up

create table #guest(id int identity(1,1), guest_name varchar(50))
go
insert into #guest(guest_name) select guest_name from guest
go
update room set guest_name = null
go
update room set guest_name = #guest.guest_name
from room, #guest
where room.num = #guest.id

I'd go for the second solution any time of the day, even though I have
come up with a pure SQL solution just as well. Simplicity in my opinion
is a great advantage. So, even though I know pretty well, for more than
a decade, that "rows are not records etc." I also know that there are
real life situations when numbering rows comes very handy.

What do you think?

[Erland Sommarskog]

AK (st***********@gmail.com) writes:

OK, I've got a hotel with a dozen empty rooms, and a bunch of guests
arriving at once.
Using ROW_NUMBER(), available in DB2 and Oracle,

And not only that - it's in SQL 2005 as well. A great addition!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Mike R (ne**@mikeread.freeserve.co.uk) writes:

Thanks Simon - great links, In the end I wrote a cursor in my SQL
function to add the identifier.

That is not likely to be good for performance. Using a temp table
or a table variable with an IDENTITY column as demonstrated in
other posts, is probably the best way to go.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp