SQL query to fetch unique entries. Is the query correct and efficient?

The table structure is provided below. query used,

desc p2p

Output

'auto_id','bigint(20)','NO','PRI',NULL,'auto_increment'
'created_at','datetime','NO','',NULL,''
'expiry_date','datetime','YES','',NULL,''
'last_updated_at','datetime','NO','',NULL,''
'platform','int(11)','YES','',NULL,''
'receiver_id','varchar(255)','YES','',NULL,''
'receiver_mobile_number','varchar(12)','YES','',NULL,''
'registered_user','tinyint(1)','YES','',NULL,''
'sender_id','varchar(255)','NO','MUL',NULL,''
'sender_mobile_number','varchar(12)','NO','',NULL,''
'txn_amount','decimal(19,2)','NO','',NULL,''
'txn_meta_data','json','YES','',NULL,''
'txn_status','int(11)','NO','',NULL,''
'txn_type','int(11)','NO','',NULL,''
'version','int(11)','YES','',NULL,''

I want to fetch the latest 10 unique receiver_mobile_number from the table for a specific sender_id and txn_type in (2,4). The problem here arises when the receiver mobile number is duplicate. For example, if I have two rows with the same receiver_mobile_number, then I want the latest entry, ie order by created_at desc top entry.

I have made the following query

select MAX(txn_type) as txn_type, MAX(txn_status) as txn_status, MAX(sender_mobile_number) as sender_mobile_number, MAX(sender_id) as sender_id, MAX(registered_user) as registered_user, MAX(receiver_id) as receiver_id, MAX(platform) as platform, MAX(last_updated_at) as last_updated_at, MAX(expiry_date) as expiry_date, MAX(txn_amount) as txn_amount, MAX(auto_id) as auto_id, MAX(version) as version,receiver_mobile_number, MAX(created_at) as created_at, MAX(txn_meta_data) as txn_meta_data from p2p where sender_id = :customer_id and txn_status in (2,4) group by receiver_mobile_number order by MAX(txn_type), MAX(txn_status), MAX(sender_mobile_number), MAX(sender_id), MAX(registered_user), MAX(receiver_id), MAX(platform), MAX(last_updated_at), MAX(expiry_date), MAX(txn_amount), MAX(created_at), MAX(txn_meta_data), MAX(auto_id), MAX(version) desc limit 10

1.) Is the query correct? My exact doubt is in the usage of max function. As per my understanding, Max function will return the maximum value among the selected columns which have been grouped together, ie if the txn_amount is 10 for the latest entry and 500 for some previous entry for the same receiver mobile number, then I’ll get 500 and not 10. I actually need all the rows with the latest created_at date if 2 receiver_mobile_number are same.

2.) I need to select all the columns of the table. Do I need to use Max for every one on them? Is there not a simpler way to use * in this case?

3.) Is it the optimized query? Can this be improved? I have the index on sender_id.

4.) What are the alternative queries possible?

For ease of understanding, I have created the structure, sample data set and query. Please use the following link for online sql db

https://paiza.io/projects/Sm0hqZ-gUXXxSTCeLfwxmA?language=mysql

Please can anyone help me with these questions? Thanks.

Answer

As per your explanation in comment, Below would be the query –

SELECT * FROM p2p t2 INNER JOIN (SELECT receiver_mobile_number, Max(created_at) AS req_date FROM p2p GROUP BY receiver_mobile_number) t1 ON t2.created_at = t1.req_date AND t2.receiver_mobile_number = t1.receiver_mobile_number;

Above query will fetch all the records from table with latest created_at column.

If you need latest 10 unique receiver_mobile_number from the table for a specific sender_id and txn_type, then below would be the query.

SELECT t2.receiver_mobile_number
FROM   p2p t2
       INNER JOIN (SELECT receiver_mobile_number,
                          Max(created_at) AS req_date
                   FROM   p2p
                   WHERE  sender_id = '<seneder_id>'
                          AND txn_type IN ( 2, 4 )
                   GROUP  BY receiver_mobile_number) t1
               ON t2.created_at = t1.req_date
                  AND t2.receiver_mobile_number = t1.receiver_mobile_number
ORDER  BY t2.created_at DESC
LIMIT  10; 

And the query which you have written would pick the max value of columns for the entire table, You have to provide group by clause if you want aggregated columns for that group.

Hope this helps