0

in sql, i want to do something like

update table set col = rank(col) order by col

how do i do this ?

Purpose :

currently the col has extreme values, like -14000, 23, 4000, 23000 ... they are used in asc, desc sort but when i plot them on a slider, having say 10 positions, each slider position has highly uneven data, so to even it out i wish to renumber the column, 

-14000 becomes 0
23 becomes 1
4000 becomes 2

and so on

pilcrow
  • 56,591
  • 13
  • 94
  • 135
Pheonix
  • 6,049
  • 6
  • 30
  • 48

2 Answers2

3

Used this :

update table set col = (select count(*) from (select col from table) as temptable where temptable.col <table.col );
Pheonix
  • 6,049
  • 6
  • 30
  • 48
0

In SQL Server, you can use two subqueries and the ROW_NUMBER function. In cases where you have duplicates in col, the ranking will follow standard competition ranking.

Sample script:

SELECT * INTO #TABLE
FROM
(
    select -14000 col
    union all SELECT 23
    union all select 4000
    union all SELECT 23 --sample duplicated data
) Unioned

UPDATE #TABLE
SET col =
(
    SELECT top 1 rowNum
    FROM
    (
        SELECT 
            col
            , row_number() OVER (order by col) - 1 rowNum --starts at 0 rank
        FROM #TABLE
    ) MySubQuery
    WHERE MySubQuery.col = #TABLE.col
)
Peter Majeed
  • 5,304
  • 2
  • 32
  • 57
  • i am worried about this part of your query : ` select -14000 col union all SELECT 23 union all select 4000 union all SELECT 23 --sample duplicated data ` my data in the question was just an example, i have 120,000 rows... do i have to list all in there ? – Pheonix Jan 24 '12 at 17:13
  • @Pheonix: Definitely not! I only included the `SELECT INTO` statement in the example to provide a proof of concept. For your real table, you can just use the `UPDATE` statement and replace `#Table` with your real table name. Of course, creating a temp table to test the update first wouldn't be such a bad idea... – Peter Majeed Jan 24 '12 at 17:56
  • sorry it doesnt works, probably problem is something specific to drizzle :: `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '1 rowNum FROM ( SELECT sw , row_number(' at line 3` – Pheonix Jan 24 '12 at 18:20
  • @Pheonix: Right; this is because `ROW_NUMBER` is not available in MySQL. There are several answers @ SO that translate the `ROW_NUMBER` function to MySQL, so if you can't find the right translation and if someone else doesn't post a MySQL specific solution, I'll try to post an edit later tonight. – Peter Majeed Jan 24 '12 at 18:33