CODEWITHSUNDEEP
mysql
Fury
Fury

Reputation: 4776

how to get number of row (where the record id standing)

Here is instalments table. I'd like to get where instalments.id 46 standing which is 3rd row.

Please make sure look at order number.

The first column is a value that result should return.

     |  id  | order_id |
------------------------
  1  |  23  | OOO12345 |
------------------------
  2  |  45  | OOO12345 |
------------------------
  3  |  46  | OOO12345 |
------------------------
  4  |  61  | OOO12345 |

------------------------
  1  |  62  | OOO12346 |
------------------------
  2  |  63  | OOO12346 |
------------------------
  3  |  64  | OOO12346 |
------------------------

instalments.id = 46  =>  3 
instalments.id = 63  =>  2


SELECT  wr.order_id, i1.previous_rows
        // other fields from other joined tables
FROM writers.work_records wr
LEFT JOIN writers.instalments i ON i.id = wr.instalment_id
LEFT JOIN (
     SELECT COUNT(id) as previous_rows, order_id, id
     FROM instalments
) AS i1 ON i1.order_id = wr.order_id AND i1.id <= wr.instalment_id
WHERE 
    wr.order_id = '00012345'
    conditions

Any help please.

Upvotes: 1

Views: 61

Answers (2)

Sai Kiran Sripada
Sai Kiran Sripada

Reputation: 1147

SQL:

SET @rownum = 0;

SELECT id
FROM
  (SELECT id
   FROM instalments
   WHERE id = 46) rownum;

OR

SELECT id
FROM
  (SELECT id
   FROM instalments,
     (SELECT @rownum:=0) r) rownum
WHERE id = 46

Upvotes: 0

gabe3886
gabe3886

Reputation: 4265

Assuming your IDs are in order, you could select the number of rows which have an ID lower than or equal to the ID you want:

SELECT COUNT(id) as previous_rows
FROM installments 
WHERE id <= 46

Upvotes: 1

Related Questions