CODEWITHSUNDEEP
mysqlsql
M.B Kakadiya
M.B Kakadiya

Reputation: 576

Mysql, Check field value change?

Mysql, Check field value change? if change the value then show different value

table: a

   id  size  name
   --------------
   1   500   abc
   2   200   bcd
   3   400   xyz
   4   500   bbb
   5   200   aaa


  select @s:=@s+1 as index, id, size, name from a,(SELECT @s:= 0) AS s order by size;

In this query data like.

   index id  size  name
   --------------------

   1     2   200   bcd
   2     5   200   aaa
   3     3   400   xyz
   4     1   500   abc
   5     4   500   bbb

I need to get index change when size is change. I want to get this type of data. like.

   index id  size  name
   --------------------

   1     2   200   bcd
   1     5   200   aaa
   2     3   400   xyz
   3     1   500   abc
   3     4   500   bbb

Upvotes: 3

Views: 2304

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

This should do the trick:

SELECT
  @s := @s + (@prev_size != a.size) `index`,
  id,
  @prev_size := a.size size,
  name
FROM a, (SELECT @s := 0, @prev_size := -1) s
ORDER BY a.size

Output:

| INDEX | ID | SIZE | NAME |
|-------|----|------|------|
|     1 |  2 |  200 |  bcd |
|     1 |  5 |  200 |  aaa |
|     2 |  3 |  400 |  xyz |
|     3 |  1 |  500 |  abc |
|     3 |  4 |  500 |  bbb |

Fiddle here.

Upvotes: 2

Related Questions