Select most recent row from table in MySQL

Databases
,
1

I am trying the get the last entry from the table group by type.
I tried to use sub select method but it was not working.
Please help how I achieve the goal
and get the expected output.

ID | type    | value | key
-----------------------------------
1  | Mango   | 10    | AP111
2  | Mango   | 20    | AP123
3  | Mango   | 50    | AP123
4  | Banana  | 20    | AP123
5  | Banana  | 20    | AP123

SELECT * FROM table WHERE ID = (SELECT MAX(ID) FROM table) AND key = AP123 GROUP BY type

Expected Output

3  | Mango   | 50    | AP123
5  | Banana  | 20    | AP123

but this is not working

2

Two WHERE? Try to replace the second WHERE with AND

3

posting type typos, sorry I had updated

4

Depending on you database you probably can use

SELECT * FROM tbl
HAVING MAX(ID) AND key = 'AP123'
GROUP BY type
5

This will not working

6

The sub-query needs to be the rest of that query. You are trying to get the maximum id per GROUP. You also need to use WHERE ID IN(…), because the sub-query will return a set of ids, one per group.

7

thank you but I not understand, could you please elaborate my by code

8

please defne which entry is “last entry”

9

for Mango ID 3 is last and for Banana ID 5 is last

10

I did a little test script on my local server and this seems to work doing it this way?

// The SQL statement selects all columns from rows in the FruitTable where
// each row has the maximum ID for its type and the key equals 'AP123'.
$sql = "SELECT t1.*
FROM FruitTable t1
JOIN (
  SELECT type, MAX(ID) as maxID
  FROM FruitTable
  WHERE `key` = :key
  GROUP BY type
) t2
ON t1.type = t2.type AND t1.ID = t2.maxID ORDER BY ID";

$stmt = $pdo->prepare($sql);
$stmt->execute(['key' => 'AP123']);
$data = $stmt->fetchAll();
1 Like
11

genius, but also if there are no any duplicate it must show as single like if

ID | type    | value | key
-----------------------------------
1  | Mango   | 10    | AP111
2  | Mango   | 20    | AP123
3  | Mango   | 50    | AP123
4  | Banana  | 20    | AP123
5  | Banana  | 20    | AP123
6  | Grapes  | 20    | AP123

output will

3  | Mango   | 50    | AP123
5  | Banana  | 20    | AP123
6  | Grapes  | 20    | AP123
12

Double check your version of the query, because the one @Pepster64 gave you should do exactly that. A quick guess was you included the value field in the sub-query, which you don’t want to have.

13

absolutely true – i can see at least 4 things wrong with it

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.