MySql Select rows based on other rows

Question

This is my current result from MySQL table tbl_msresponse

MobileNo         Time                     Status
xxxxx69333       2019-09-15 13:11:54      FAILED 
xxxxx69333       2019-09-15 14:12:16      FAILED
xxxxx69333       2019-09-15 14:21:34      DELIVERED **<- this row required**
xxxxx88779       2019-09-15 13:11:54      FAILED **<- this row required**
xxxxx76555       2019-09-15 14:12:16      FAILED
xxxxx76555       2019-09-15 14:21:34      DELIVERED **<- this row required**

From the above I want

 MobileNo        Time                     Status
 xxxxx69333      2019-09-15 14:21:34      DELIVERED 
 xxxxx88779      2019-09-15 13:11:54      FAILED 
 xxxxx76555      2019-09-15 14:21:34      DELIVERED

So, now I want only DELIVERED row. If there is no DELIVERED row, I want show only last FAILED row of MobileNo based on Time col. How can I achieve this in MySQL?

@P.Salmon Sorry, it is FAILED or DELIVERED. Will update my question — Learner, Sep 16 '19 at 10:45
@SudhirOjha Sorry, I am just selection `select * from tbl_msresponse` only, now I wan t filter those records which I dont know how to :( — Learner, Sep 16 '19 at 10:48
@Learner i think you have to create a store procedure for that. — Sudhir Ojha, Sep 16 '19 at 11:23

score 0 · Answer 1 · answered Sep 16 '19 at 11:11

0

You can use limit for take just 1 result, if you want to last you need to use DESC keyword otherwise ASC you can write like this:

Select * from table_name where Status="Delivered" or Status = Select status from table_name where Status="Failed" order by Time DESC LIMIT 1;

answered Sep 16 '19 at 11:11

Mert Akkanat

111
7

I have updated my question. Please help me – Learner Sep 16 '19 at 23:42
Got answer here : https://paulund.co.uk/get-last-record-in-each-mysql-group – Learner Oct 07 '19 at 03:49

score 0 · Answer 2 · edited Oct 12 '19 at 16:54

0

If you with to use stored procedure, then here is my solution using stored procedure. Actually you can write if/else condition inside stored procedure. The condition basically check if any row exists with DELIVERED status then return all rows else return FAILED status row order by Time and limit is 1.

Here is procedure:

DELIMITER //
create procedure my_procedure()
begin
    IF NOT EXISTS (SELECT * FROM my_response where status='DELIVERED')
    THEN
        SELECT * from tbl_msresponse WHERE Status='FAILED' order by Time DESC LIMIT 1;
    ELSE
        SELECT * from tbl_msresponse WHERE Status='DELIVERED';
    END IF;
end //
DELIMITER;

And you can call this procedure like:

call my_procedure();

edited Oct 12 '19 at 16:54

marc_s

732,580
175
1,330
1,459

answered Sep 16 '19 at 12:44

Sudhir Ojha

3,247
3
14
24

Can I use with multiple mobile numbers ? – Learner Sep 16 '19 at 12:58
Yes you can. No restriction on it. – Sudhir Ojha Sep 16 '19 at 12:59
It is showing either DELIVERED or FAILED if I use multiple numbers. I want want both number's status like `94444xxx-delivered`, `9888xxx-failed`, `9666xxx-delivered` like in a single query – Learner Sep 16 '19 at 13:23
Updated my question. I want like this result – Learner Sep 16 '19 at 13:26
1

Got answer here : https://paulund.co.uk/get-last-record-in-each-mysql-group – Learner Oct 07 '19 at 03:49

score 0 · Answer 3 · answered Sep 17 '19 at 06:35

0

Assuming status is binary (ie failed or delivered) then discussion of status is irrelevant, all you need is the most recent entry by mobileno

select t.*
from t 
where time = (select max(time) from t t1 where t1.mobileno = t.mobileno);

+----------+---------------------+-----------+
| MobileNo | Time                | Status    |
+----------+---------------------+-----------+
|    69333 | 2019-09-15 14:21:34 | DELIVERED |
|    88779 | 2019-09-15 13:11:54 | FAILED    |
|    76555 | 2019-09-15 14:21:34 | DELIVERED |
+----------+---------------------+-----------+
3 rows in set (0.00 sec)

answered Sep 17 '19 at 06:35

P.Salmon

17,104
2
12
19

Thanks. It is working, but very slow. Taking 4sec for a row. I have more than 5000 rows to filter from. Any optimization? – Learner Sep 17 '19 at 09:09
4 secs a row is unexpectedly slow. Can you add your table definition please and try adding indexe(s) on mobileno and time. – P.Salmon Sep 17 '19 at 12:52
OK, will try and update you. Thanks – Learner Sep 17 '19 at 13:03
Got answer here : https://paulund.co.uk/get-last-record-in-each-mysql-group – Learner Oct 07 '19 at 03:49

MySql Select rows based on other rows

3 Answers3