I have a table like this (uniqcode is the primary key):

uniqcode  name
--------  ----
1         bb
2         aa
3         aa
4         dd
5         cc

How could I create the following table out of the above one?

row_num   name   uniqcode
-------   ----   --------
1          aa      2
2          aa      3
3          bb      1
4          cc      5
5          dd      4

More ng poopoo...just use RAC :)

Exec Rac
@transform='_dummy_',
@rows='uniqcode & [name]',
@rowsort='[name] & uniqcode',
@pvtcol='Report Mode',
@from='##T',
@defaults1='y',
-- Get your ranks by just typing a @rows column and give it a name.
-- Real difficult:) @rowindicators is one of several rank options.
@rowindicators='uniqcode{row_num}',
@select='select row_num,[name],uniqcode
               from rac
                 order by row_num'

row_num name uniqcode
-------     ----    --------
1               aa             2
2               aa             3
3               bb             1
4               cc              5
5               dd             4

Tested

Any ranking you can do in S2005 you can do with RAC in S2k.
But the reverse is not true.
As the say, "the 'SEQUEL' is usually not as good as the original" :)

Visit the original RAC @
www.rac4sql.net

best,
http://racster.blogspot.com/

Steve Dassin wrote:
is Rac your special SP?

More to the point, it could be 'your' special SP :)

Why do I suddenly feel like I need a shower just from being in proximity to
this conversation?

Hi Mike,

Do tell... Inquiring minds would like to know!

best,
steve

Show quoteHide quote

When I read "it could be 'your' special SP" I thought I heard faint cheesy
70s porno music in the background.


Show quoteHide quote

Steve Dassin wrote:
Could you post the source of your RAC?

Hi,

RAC is a server utility.It's a system of sp's and a few functions
written entirely in t-sql.You can purchase RAC and if you like
you can also purchase the source code.We don't not make the source
code available otherwise.
We have many users of RAC amongst which is Mike C# who
is one of our biggest advocates.It's an extremely clean system :)

best,
steve
www.rac4sql.net

Show quoteHide quote

On Mon, 28 Aug 2006 20:52:47 -0400, Steve Dassin wrote:

(snip)
Hi Steve,

I thought that Steve Dassin was the biggest RAC advocate? <g>

Hi,

Yeah, I think Mike drops RAC to a 'D FLAT' from a 'C#'  :(  :)

best (even to RAC haters:)
steve
P.S. If I was as good at programming as I am at marketing, would I be
answering a lot of posts?

Show quoteHide quote

I got nothing against RAC.  Don't even know what it is.  I was commenting on
your marketing tactics...

Show quoteHide quote

That I should have to suffer the slings and arrows
of such blatant indiffernce is at times to great
a cross to bear. Would I only be but capabable of a
resolution. For I fear that the magnitude of the crime
against me is not as great as the sin I have committed.
Such are the vicissitudes of the art of persuasion
But perhaps from such humbleness comes an added
vigor.

best,
steve

LOFL.  I'll take a look at RAC myself, right after I run whatever it is you
typed through Babelfish.

Show quoteHide quote

Please take a bow for your splendid and most appropriate response.
For my part, allow me to exclaim a very big and proud - wow.

Henceforth we will forever be joined at the hip as a 'Bow-Wow'

best,
steve

Show quoteHide quote

Man-wai,

It's easiest in SQL Server 2005:

select
  row_number() over (order by name, uniqcode) as row_num,
  name,
  uniqcode
from T

In SQL Server 2000, you have to do more work:

select
  count(*) as row_num,
  T1.name,
  T1.uniqcode
from T as T1
join T as T2
on T2.name <= T1.name
and (
  T2.name < T1.name or
  T2.uniqcode <= T1.uniqcode
)

or more readably, but often less efficiently:


select
  count(*) as row_num,
  T1.name,
  T1.uniqcode
from T as T1
join T as T2
on T2.name < T1.name
or (
  T2.name <= T1.name and
  T2.uniqcode < T1.uniqcode
)

[not tested]

Steve Kass
Drew University
www.stevekass.com

Man-wai Chang wrote:

Show quoteHide quote

Thanks

Where is the GROUP BY clause for the count(*)?

On Tue, 29 Aug 2006 09:10:03 +0800, Man-wai Chang wrote:

Show quoteHide quoteHi Man-wai,

It's missing. Apparently, Steve either forgot to type it or left it out
when copying and pasting his code. You need to add this GROUP BY clause
to get it to work:

GROUP BY T1.name, T1.uniqcode

Hello Man-wai Chang,

Okay, aside from all the BS about RAC, did anybody ever answer the question?
In SQL Server 2005, you can do this:

use scratch
go
set nocount on
create table t(uniqcode tinyint identity(1,1) primary key,name char(2))
go
insert into t(name) values ('bb')
insert into t(name) values ('aa')
insert into t(name) values ('aa')
insert into t(name) values ('dd')
insert into t(name) values ('cc')
go
select row_number() over (order by name) as row_num,name,uniqcode from t
go
drop table t

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

The question pales in significance given your lack of recognition of
such exquisite expediency. Such surgical dissection is surely misplaced
and perhaps should be directed to those activities at less than arms length.
The thread has turned into a test of ones fortitude. I ask for strength
least I
may be required to part in such sweet sorrow.

best,
steve


Show quoteHide quote

a question for the soldiers on the line
Need advice on Backup
Explicit insert for Timestamp column in Insert Trigger
nText,Text,Image field problem for Update/Delete Trigger
Returned first row of a column in a group by