从单个表中获取两列，其中一列是最大值，另一列是最大列

Question

Table: XYZ
+----+-----------+--------+---------+
| ID | parent_ID | status | user_ID |
+----+-----------+--------+---------+
| 1  | 432       | public | 23      |
| 5  | 432       | public | 1       |
| 10 | 432       | public | 14      |
| 24 | 432       | public | 4       |
+----+-----------+--------+---------+

If this is the scenario where ID is an auto increment field I can grab the last ID by the following query:

SELECT MAX(ID)
FROM XYZ
WHERE parent_ID = 432
   AND status = 'public'

How can I grab the two fields: ID, and user_ID using a single query so that I can have the following result:

array(
   'ID' => 24
   'user_ID' => 4
)

That means the Maximum value of ID where parent_ID is 432 and user_ID of that row of ID.

I have seen this answer and tried, but in my case I don't have group by ID and max ID different. In my case everything is ID. I know I can have them all, then can run a foreach to grab the max one in PHP way, but I think a single SQL query is much faster here.

Any help and suggestion would be greatly appreciated.

Answer 1

Try this:

SELECT ID, user_ID
FROM XYZ AS x
INNER JOIN (SELECT MAX(ID) AS maxID
            FROM XYZ
            WHERE parent_ID = 432 AND status = 'public') t
ON x.ID = t.maxID

Or, alternatively:

SELECT ID, user_ID
FROM XYZ 
WHERE ID = (SELECT MAX(ID) AS maxID
            FROM XYZ
            WHERE parent_ID = 432 AND status = 'public')