CODEWITHSUNDEEP
mysqlsql-update
ZajcTeGleda
ZajcTeGleda

Reputation: 89

mysql update table with sort by price column

I have table with data like this:

Id   Item     Price
1      a       2
2      a       1
3      b       7
4      a       2,3
5      c       66
6      c       50,9
7      b       8,2
8      a       4
9      c       55,2

is it possible to create update query that will give me result

 Id   Item     Price      Sort
    1      a       2        2
    2      a       1        1
    3      b       7        1
    4      a       2,3      3
    5      c       66       3
    6      c       50,9     1
    7      b       8,2      2
    8      a       4        4
    9      c       55,2     2

or better view orderd by item and price

 Id   Item     Price      Sort
    2      a       1        1
    1      a       2        2
    4      a       2,3      3
    8      a       4        4
    3      b       7        1
    8      b       8,2      2
    6      c       50,9     1
    9      c       55,2     2
    5      c       66       3

Upvotes: 0

Views: 134

Answers (1)

Strawberry
Strawberry

Reputation: 33935

Here's one way, although it doesn't scale well, and you may want to think about how to handle ties...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Item     CHAR(1) NOT NULL
,Price DECIMAL(5,2)
);

INSERT INTO my_table VALUES
(1      ,'a',       2),
(2      ,'a',       1),
(3      ,'b',       7),
(4      ,'a',       2.3),
(5      ,'c',       66),
(6      ,'c',       50.9),
(7      ,'b',       8.2),
(8      ,'a',       4),
(9      ,'c',       55.2);

SELECT x.*
     , COUNT(*) rank 
  FROM my_table x 
  JOIN my_table y 
    ON y.item = x.item 
   AND y.price <= x.price 
 GROUP 
    BY x.id 
 ORDER 
    BY item,rank;
+----+------+-------+------+
| Id | Item | Price | rank |
+----+------+-------+------+
|  2 | a    |  1.00 |    1 |
|  1 | a    |  2.00 |    2 |
|  4 | a    |  2.30 |    3 |
|  8 | a    |  4.00 |    4 |
|  3 | b    |  7.00 |    1 |
|  7 | b    |  8.20 |    2 |
|  6 | c    | 50.90 |    1 |
|  9 | c    | 55.20 |    2 |
|  5 | c    | 66.00 |    3 |
+----+------+-------+------+

Where performance is an issue, the conventional response looks something like this...

SELECT id
     , item
     , price
     , rank
  FROM 
     ( SELECT *
            , @pitem := @citem
            , @pprice := @cprice
            , @citem := item
            , @cprice := price
            , @rank := IF(@pitem = @citem, IF(@pprice = @cprice,@rank,@rank+1), @rank:=1) rank
         FROM my_table
         , (SELECT @citem := null, @pitem := null, @cprice = null, @pprice = null, @rank := 0) vars
        ORDER 
           BY item,price
     ) x;

+----+------+-------+------+
| id | item | price | rank |
+----+------+-------+------+
|  2 | a    |  1.00 |    1 |
|  1 | a    |  2.00 |    2 |
|  4 | a    |  2.30 |    3 |
|  8 | a    |  4.00 |    4 |
|  3 | b    |  7.00 |    1 |
|  7 | b    |  8.20 |    2 |
|  6 | c    | 50.90 |    1 |
|  9 | c    | 55.20 |    2 |
|  5 | c    | 66.00 |    3 |
+----+------+-------+------+

Alternatively, you can handle this sort of stuff just as well in application-level code

Upvotes: 2

Related Questions