dcsimg
Results 1 to 6 of 6

Thread: a incremental column?

  1. #1
    Join Date
    Apr 2005
    Posts
    7

    a incremental column?

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    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"

  3. #3
    Join Date
    Apr 2005
    Posts
    7
    works great!

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

    cheers!

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  5. #5
    Join Date
    Apr 2005
    Posts
    7
    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!

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •