Solved

Increment / Rank in SQL

Posted on 2004-10-07
9
492 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

12 Experts available now in Live!

Get 1:1 Help Now