Can I safely use two MAX queries in an SQL query

Question

I have a table with several hundred thousand records, and I'm trying to use a query to get a set of results for a specific recipient id and group them by sender_id. My current SQL query is working, but I want to know if there could be any potential problems using the two MAX calls in the statement. It looks like this:

SELECT MAX(id) as id, sender_id, receiver_id, MAX(date) as date FROM messages WHERE receiver_id=5 and belong_to=5 GROUP BY sender_id 

The table date looks like this:

 id sender_id receiver_id content date belong_to -- --------- ----------- ------- ------------------- --------- 1 5 7 test 2013-03-11 10:33:54 7 2 5 7 test 2013-03-11 10:33:54 5 3 13 7 test 2 2013-03-13 12:01:36 7 4 13 7 test 2 2013-03-13 12:01:36 13 5 5 7 test 3 2013-03-14 09:15:37 7 6 5 7 test 3 2013-03-14 09:15:37 5 7 25 5 data1 2013-03-15 11:01:36 5 8 25 5 data1 2013-03-15 11:01:36 25 9 16 5 data2 2013-03-17 09:17:17 5 10 16 5 data2 2013-03-17 09:17:17 16 11 25 5 data3 2013-04-05 09:17:17 5 12 25 5 data3 2013-04-05 09:17:17 16 

The result of my query is as follows:

 id sender_id receiver_id date -- --------- ----------- ------------------- 9 16 5 2013-03-17 09:17:17 11 25 5 2013-04-05 09:17:17 

Are there any problems with this query using MAX calls? If so, what is the alternative?

Answer 1

I don’t quite understand your structure (for example, this example assumes that a sender_id, receiver_id, date, belong_to may be superimposed UNIQUE), but I suspect you want something like this. Filter as needed.

 SELECT x.* FROM messages x JOIN ( SELECT sender_id , receiver_id , MAX(date) max_date FROM messages GROUP BY receiver_id , sender_id ) y ON y.sender_id = x.sender_id AND y.receiver_id = x.receiver_id AND y.max_date = x.date WHERE x.belong_to = x.receiver_id; 

Answer 2

Based on the comments you want:

'unique [list of sender_ids for a specific recipient_id with the most recent record (date) for each [receiver]

If you mean ALL Senders that have the most recent registration date for this single receiver, then:

 Select * From Messages m Where date = (Select Max(date) From messages Where receiver_id = m.receiver_id) And receiver_id = 5 -- add this if you only want results for one receiver_id 

If so, you mean 'a unique list of sender_ids for a specific recipient_id with the most recent entry (date) for each [recipient-sender combination], and then do it

 Select * From Messages m Where date = (Select Max(date) From messages Where Sender_id = m.Sender_id And receiver_id = m.receiver_id) And receiver_id = 5 -- add this if you only want results for one receiver_id