Solved

Increment / Rank in SQL

Posted on 2004-10-07
9
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
Remove () 10 41
mssql 7 32
Email SQL Applications not able send to 4 or more recipients 2 33
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

734 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