CODEWITHSUNDEEP
mysqldatabase
Deekshith Banjan
Deekshith Banjan

Reputation: 93

How to fetch previous row value from database?

Suppose, I have the below table, let it be table "A".

Select * FROM A;
  ID       Score
  1        25
  2        36
  3        12
  4        14

I want to query that selects score in ascending order and also prev score value(stored in newly created column "prev_score") like the one shown below.

  ID       Score      Prev_Score
  3        12            0
  4        14            12
  1        25            14
  2        36            25

Can this be done using a single sql query?

Upvotes: 0

Views: 116

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do this using a correlated subquery that gets the previous value:

SELECT t1.ID, t1.Score, 
       COALESCE((SELECT Score
                 FROM mytable AS t2
                 WHERE t2.Score < t1.Score
                 ORDER BY t2.Score LIMIT 1), 0) AS Previous
FROM mytable AS t1
ORDER BY t1.Score DESC;

Demo here

Edit:

If you want score in ascending order then just switch the ASC / DESC keyword:

SELECT t1.ID, t1.Score, 
       COALESCE((SELECT Score
                 FROM mytable AS t2
                 WHERE t2.Score > t1.Score
                 ORDER BY t2.Score DESC LIMIT 1), 0) AS Previous
FROM mytable AS t1
ORDER BY t1.Score;

Upvotes: 2

Related Questions