Solved

Increment / Rank in SQL

Posted on 2004-10-07
9
493 Views
Last Modified: 2006-11-17
Hi guys,

SQL

I have  table with a list of values in it

ie.
Totals
100
200
300
300
400

I want to update a column called Rank
But this is not a true rank column - because I do not want this to happen

100   1
200   2
300   3
300   3
400   4

I want this result

100   1
200   2
300   3
300   4
400   5

I want to try and avoid adding it as an auto incrementing column - means I have to order by and insert the values into a new table.
Does anyone have a solution as to how I can do this.
I have code to Rank but like I said that is not actually what we need.

Thanks



0
Comment
Question by:azrakdragon
  • 3
  • 3
  • 3
9 Comments
 
LVL 8

Expert Comment

by:Jupiler78
ID: 12246423
Hi azrakdragon,

I don't use Microsoft SQL, but Sybase. But here we can use NUMBER(*)

e.g. select totals,number(*) from ...

Cheers!
0
 

Author Comment

by:azrakdragon
ID: 12246528
Sounds like a pretty kewl function - thank you - but I had a look in books online and it doesn't seem like SQL has something like that
Anybody else ???? pllleeeeease
0
 
LVL 12

Expert Comment

by:ill
ID: 12246552
select *, "counter"= identity(int, 1, 1)
into #temp
from mytable
select * from #temp
0
 
LVL 8

Expert Comment

by:Jupiler78
ID: 12246585
azrakdragon,

It find it hard to believe, that MS SQL, doesn't has a function like the one I described, so maybe another description or so. But I can't help you more because I don't have the help files.
You could look into the help files with some parts of my help description:

Generates numbers starting at 1 for each successive row in the results of the query
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:azrakdragon
ID: 12246656
Jupiler - just checked again - it only has auto increment by the looks of it
I think SQL is still lacking alot of nice functionality that other DBMS's have, however I believe that they have enhanced 2005 greatly.

Anyway ...ill -thanks but that is not what I want to do as I will have to do this will multiple tables and joins :o(
I got this off the site which ranks

create table dontest(id int, name char(5), grade int)
go
insert into dontest values(12345,'Bob',75)
insert into dontest values(23552,'Sally',60)
insert into dontest values(26643,'Beth',75)
insert into dontest values(45633,'John',95)
insert into dontest values(34632,'Bill',60)
insert into dontest values(98765,'Fred',85)
insert into dontest values(87654,'Georg',50)

select
(select count(*)+1 rank from dontest b
where a.grade < b.grade) rank,
a.name, a.grade
from
dontest a
order by rank

however I want to do the same but I don't want values that are the same ranked with the same number - it must just be a straight forward incremented number

Thanks
0
 
LVL 12

Expert Comment

by:ill
ID: 12246724
select
"counter"= identity(int, 1, 1) ,
(select count(*)+1 rank from dontest b
where a.grade < b.grade) rank,
a.name, a.grade
into #x
from
dontest a
order by grade desc
select * from #x
----------- simplyfied
select
"counter"= identity(int, 1, 1) ,
a.name, a.grade
into #x
from
dontest a
order by grade desc
select * from #x
0
 
LVL 12

Accepted Solution

by:
ill earned 500 total points
ID: 12246754
--  "order by grade" gives you results you want ?
select
"counter"= identity(int, 1, 1) ,
a.name, a.grade
into #x
from
dontest a
order by grade desc
select * from #x
0
 

Author Comment

by:azrakdragon
ID: 12247774
Ill - you are the BEST !!!!
Thank you!!!
Every time I get an answer on this site, it just enforces why I subscribe to it.
Thanks for all the help to Juliper as well.
0
 
LVL 8

Expert Comment

by:Jupiler78
ID: 12247790
You're welcome.

Good answer Ill
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now