Solved

Increment / Rank in SQL

Posted on 2004-10-07
9
502 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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