How to order by from specific value in mysql?

Question

Scenario: I am making a medical report where I need the latest test result plus two past results (if the customer has taken the test in the past).

Consider this db:

id | patient_id | test_id | value
 1 |     3      |   5     |  10
 2 |     6      |   8     |  14
 3 |     3      |   5     |  12
 4 |     9      |   3     |  18
 5 |     3      |   10    |  4
 6 |     4      |   15    |  10
 7 |     3      |   5     |  19
 8 |     3      |   5     |  29

So if the patient comes and asks for the report [row = 7], I need to show results of the past two records [row = 3 and row = 1] having the same patient_id and test_id.

What I have tried:

 $records = mysqli_query($con, "select * from value_based_result where patient_id='$ptid' and test_id='$testid' order by (id='$id') DESC, id  limit 3 ") or die(mysqli_error());
 while($record=mysqli_fetch_array($records)){ 
       echo $record['value'];                     
 }

Now the issue is, this query is not starting ordering by from current (id=7). It is just bringing (id=7) result to the top in query. What is the best way to achieve this?

Not entirely sure, but maybe this thread is of any help: https://stackoverflow.com/questions/8322849/mysql-order-by-specific-id-values — Sem, May 18 '21 at 06:45
And see about sql injection and the importance of prepared and bound/executed queries — Strawberry, May 18 '21 at 06:47

score 1 · Accepted Answer · answered May 18 '21 at 06:47

You include the id in the order by, but you need to use this as part of the where clause to give a starting point. Assuming that 7 is the last one you want to fetch and all others will be less than that...

select * 
    from value_based_result 
    where patient_id=? 
        and test_id=?
        and id <= ?
    order by id desc  
    limit 3

You need to also look into how to use prepared statements - How can I prevent SQL injection in PHP?.

As well as how to work with errors - mysqli_fetch_assoc() expects parameter / Call to a member function bind_param() errors. How to get the actual mysql error and fix it?

score 0 · Answer 2 · answered May 18 '21 at 06:48

The ROW_NUMBER analytic function comes in handy here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY patient_id, test_id
                                 ORDER BY id DESC) rn
    FROM value_based_result
    WHERE id <= 7
)

SELECT id, patient_id, test_id, value
FROM cte
WHERE
    (patient_id, test_id) IN (SELECT patient_id, test_id
                              FROM value_based_result
                              WHERE id = 7) AND
    rn <= 3
ORDER BY
    id DESC;

How to order by from specific value in mysql?

2 Answers2

Demo