Solved

Increment / Rank in SQL

Posted on 2004-10-07
9
496 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to keep a record with the highest value 3 39
Rebooting Witness SQL Server 2 25
SQL Server Trigger 8 37
T-SQL and CLR parameter strings 9 23
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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