CODEWITHSUNDEEP
phpmysql
dragonpunch
dragonpunch

Reputation: 5

MySQL php - How can I get last 3 rows but not count duplicates

Hello I want to get the last 3 unique rows but allow any duplicates with the same filename to also be returned. In the example below I should get 5 rows back.

So I should get visa2 f9ed287 visa1 f9ed287 visa f9ed287 rose 996fa07 and feel e96ba65

Emo Table
---------

id  | s     | filename
----------------------
59  | visa2 | f9ed287
58  | visa1 | f9ed287
57  | visa  | f9ed287
56  | rose  | 996fa07
55  | feel  | e96ba65
54  | kick  | c716cea
53  | yay   | 8e1ff5d

This the code I am using which does not include duplicates in the result

$statement = $pdo->prepare("SELECT s, f FROM emos
                             GROUP BY (f)
                             ORDER BY id DESC                                 
                             LIMIT 3");
$statement->execute(array($room));

Upvotes: 0

Views: 129

Answers (3)

didier2l
didier2l

Reputation: 305

This should work by joining emos on herself to get last three filename :

SELECT e1.s, e1.f 
FROM emos e1
JOIN (
    SELECT f 
    FROM emos 
    GROUP BY f 
    ORDER BY id DESC 
    LIMIT 3
) e2 ON e1.f=e2.f
ORDER BY e1.id DESC;

Upvotes: 0

iubema
iubema

Reputation: 349

Try with this query:

SELECT e.s, e.filename 
FROM emos e
INNER JOIN (SELECT filename FROM emos GROUP BY filename ORDER BY id desc LIMIT 3) as t ON t.filename = e.filename

Upvotes: 1

David K-J
David K-J

Reputation: 928

Your question is not well formed, but I believe this is what you want:

SELECT s, f
FROM emos
WHERE
    f IN (SELECT DISTINCT(f) FROM emos ORDER BY id DESC LIMIT 3)
ORDER BY f, id;

If I have misunderstood, please reword your question and I will update my answer

Upvotes: 0

Related Questions