October 22, 2009 at 4:30 pm
Please see the attached screen shot in which I create a dummy result set and then run a separate query to rank the records in that result set.
For my intended purposes, instead of ordering the Field2 value alphabetically, I need it to be ordered according the original result set. In other words, I need the output to look like this:
Field1 Field2
------ ------
1 hjkhli
2 tretre
3 jipojp
4 cvnbcd
5 njklkj
Can anyone offer suggestions on how I would accomplish this?
October 22, 2009 at 4:47 pm
Hi Mattie:
Next time, PLEASE cut and paste the statements you took a screen shot of. It's lovely, but it forces us to retype everything before we can play with a solution. Help us help you. 😉
Thanks.
P.S. I am going to type up what you should've just pasted in as text. This will enable others to play with it, but it will take me a few minutes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2009 at 5:12 pm
First, there is a concept you need to understand:
Rows in SQL result sets have no "original" order, other than that which you impose on them with an ORDER BY clause. The database engine reserves the right to store sort, group and manipulate at it's convenience. It is absolutely necessary to have some column(s) which you can ORDER BY.
In the following modification to your code, the original table was changed to add an identity column called [origID]. This column serves as a reference point that preserves the original order in which rows were inserted.
The modified select clause still uses Row_Number() to assign sequence numbers based on the contents of [Field1], but then orders by [origID]. The convention of sorting by a column position rather than by name has been deprecated and should be discontinued.
create table #t (origID int identity(1,1), field1 char(6))
insert into #t values('hjkhli')
insert into #t values('tretre')
insert into #t values('jipojp')
insert into #t values('cvnbcd')
insert into #t values('njklkj')
-- produces original order
select row_number() over(order by field1) as field1, field1 as field2
from #t
order by origID
-- produces alpha list
select row_number() over(order by field1) as field1, field1 as field2
from #t
order by field1 -- (field1 as assigned in the select clause, not the original field1)
drop table #t
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply