1

using MySQL I need to fill a column with the position in the list using ORDER BY.

I saw this post : mysql-get-row-position-in-order-by

The problem with the above post (2nd solution), is that it, when it encounters the same values in the list, the position is the same, but 'gaps' appear for the next record(s). I want the positions to be consecutive.

Let's say I have a list like this :

   id   val
    A    3
    B    5 
    C    2
    D    6
    E    1
    F    8
    G    2
    H    6

I would like to get an ordered output with a position column like this :

   id   val        pos
    E    1          1
    C    2          2
    G    2          2
    A    3          3
    B    5          4
    D    6          5
    H    6          5 
    F    8          6
Community
  • 1
  • 1
Dylan
  • 9,129
  • 20
  • 96
  • 153
  • 1
    If just possible, do this in whatever you use to process the query result, and you won't have to do potentially expensive sub-queries and such, plus your query remains cleaner. – reko_t May 02 '11 at 12:22
  • I agree, I won't use such a query to do this in realtime, but I'd like to have a query to fill the position column. Of course, I could do this in PHP, but I think it's cleaner to just use a query to do this. – Dylan May 02 '11 at 12:26
  • I agree, why not just order by value, then programatically add in the positions where values are repeated? – John Kane May 02 '11 at 12:30

2 Answers2

1

What about this:

  SELECT `id`,
         (SELECT COUNT(DISTINCT `val`) + 1
            FROM `table`
           WHERE `val` < `outer`.`val`) AS `pos`,
         `val`
    FROM `table` `outer`
ORDER BY `val`

Just've taken my answer from that thread and changed it a little.

But as @reko_t mentioned in the comments - I personally vote for doing this in programming language.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • @nick rulez: actually weird. Nested correlated query with distinct count :-S Your is **much more** performant. +1 – zerkms May 02 '11 at 12:45
  • Very strange. I've tried an explain with val indexed and you query use the index while my query doesn't use it. – Nicola Cossu May 02 '11 at 12:52
  • @nick rulez: it uses index because seems like correlated subquery is transformed to some sort of join by optimizer. That is why you see the index. Outer query should be evaluated without indexes. So - it is ok. Your query still does nothing just selects everything and do simple math, while mine performs join. – zerkms May 02 '11 at 12:55
  • Thanks for your explanation. You're always very kind. :) – Nicola Cossu May 02 '11 at 12:56
  • This seems to be VERY slow (several minutes) on a recordset with 130.000 records, while Nick's solution was fast (1,5 sec) – Dylan May 02 '11 at 13:18
  • @Dylan: yes, it is expected (for me, not for Nick) result ;-) – zerkms May 02 '11 at 13:35
1
select id,val,
@pos := if(@prev<>val,@pos+1,@pos) as pos,
@prev := val as val
from table,(select @pos:=0,@prev:='') as r order by val

I agree with other advices that it would be better to do this at application level.

Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
  • Hi Dylan. Thanks but I think that zerkms solution is more efficient than mine. Accept his answer ;) – Nicola Cossu May 02 '11 at 12:46
  • @nick rulez: uhm, my answer cannot be faster by definition. @Dylan, don't believe him ;-) – zerkms May 02 '11 at 12:48
  • I don't know how my solution can work on large dataset. i'll do some benchmark ASAP. – Nicola Cossu May 02 '11 at 12:48
  • Hi zerkms. ASAP I'll do some benchmark on a large table. I'm curious to test different solutions :) – Nicola Cossu May 02 '11 at 12:49
  • Nick's solution took 1.5 sec with 130.000 records, while zerkms's solution almost crashes mysql (never ends)... – Dylan May 02 '11 at 13:15
  • Thanks for the feedback. However 1.5 seconds on 130 K records is a very long time. :( – Nicola Cossu May 02 '11 at 13:39
  • It would be nice if you do: set profiling = 1; then run the query, show profiles; and finally show profile for query X where X is the Query_ID to see what it takes a lot of time. – Nicola Cossu May 02 '11 at 13:45
  • Ok. I made a simple test. Using a stored procedure I've created a table with one million of records with a cardinality of 127 for val. It takes about 3 seconds both with mysql 5.1.36 than with 5.5.9. Profiling shows me that it takes about 0.40 seconds for sorting result and 2.4 seconds for sending data. – Nicola Cossu May 02 '11 at 14:45
  • with my data it's 0.2 sec for sorting and 1.3 sec. for sending data – Dylan May 02 '11 at 15:53
  • I don't think you can do anything better with sql. :) – Nicola Cossu May 02 '11 at 15:57