Compare rows in same table in mysql

Question

I have a mysql table like below

id   trader price
111  abc    5 
222  xyz    5.20 
333  abc    5.70 
444  xyz    5 
555  abc    5.20 

I need to compare row 1's price with row 2's price and accroding to the given sample row 2's price is less than row 1's price which means that trader xyz increased the price once, and in the same way when we compare row 2's price with row 3's price where row 3's price is higher than row 2's price which means trader abc also increased the price once. So in this manner I need to compare the entire table and find how many times each trader increased or decreased price...

I dont have any idea to do this, can someone please help me on this

Answer 1

You can perform a "self-join" (joining the table to itself) to perform queries. The tricky part here is knowing the order in which rows were inserted into the table, and only comparing rows that are sequentially (temporally) adjacent. I'm assuming you have some sort of TIMESTAMP column that will tell you which price changes came after the prior ones. If not, then perhaps "ID" can inform you of that (the greater ID row being inserted after the lesser ID).

Calling your table 'TAB', using 'TRADER' to provide the join, and using 'ID' to provide the Order, the query would require a three-way self-join such as follows:

SELECT a.trader
     , SUM(IF(a.price > b.price, 1, 0)) nbr_incr
     , SUM(IF(a.price < b.price, 1, 0)) nbr_decr
     , SUM(IF(a.price = b.price, 1, 0)) nbr_same
  FROM tab a
  JOIN tab b 
    ON a.trader = b.trader AND a.id > b.id
  LEFT OUTER JOIN tab c 
    ON a.trader = c.trader AND a.id > c.id AND b.id < c.id
 WHERE c.id IS NULL
 GROUP BY a.trader

The above query joins the table to itself twice so that each tab represents the following:

• tab a : The more recent row for comparison
• tab b : The immediately prior row to compare against
• tab c : A row between a & b timewise (should not exist)

We perform a LEFT OUTER JOIN to 'tab c' because we do not actually want that row to exist. In the where clause, we filter our results only to the results where a 'tab c' row does not exist.

Finally, the query performs a 'GROUP BY' on the trader, and SUM()s the Increments and Decrements by comparing the price from the 'a' and 'b' rows.

This was a fun challenge. Hope this helps!

john...