a incremental column?

[zcumbag]

Hi!

I have a request of having a select statement displaying a list of values in descending order and another column that tells what record it is.

something like this:

id | value | no
4 345 1
7 234 2
2 143 3
9 32 4
3 4 5

...

is this possible?

can it be something like this:

select id, value, increment(1,1) as no from tblTable
order by value desc

thanks.

[skhanal]

Try this

select a.id, a.value, count(*) as "no"
from table1 a, table1 b
where a.value <= b.value
group by a.id, a.value
order by "no"

[zcumbag]

works great!

wow, you learn something new everyday... I'd never think of doing it that way.

cheers!

[MAK]

http://www.databasejournal.com/featu...le.php/3373861

[zcumbag]

what if i wanted to find out at what top percentagea certain value ranks?

ie:

id | value | no | perc
4 345 1 1
7 234 2 40
2 143 3 60
9 32 4 80
3 4 5 100

do I make sense? I want the id with the lowest value in the table to have 100 and the one with the highest to have 1. (and everyone in between to have whatever their percentage would be, are you placed 3rd out of 4 your percentage should be 75)

anyone?

cheers!

[MAK]

select a.id, a.value, count(*) as "no", count(*)*1.00/(SELECT MAX(NO) FROM (select COUNT(*) as "no" from X aa, X bb where aa.value <= bb.value group by aa.id, aa.value) AS MYTABLE) * 100 as D
from X a, X b
where a.value <= b.value
group by a.id, a.value

3 4 5 100.0000000000000
9 32 4 80.0000000000000
2 143 3 60.0000000000000
7 234 2 40.0000000000000
4 345 1 20.0000000000000