Limit SQL Results to one result per pkey searched using "in"

Question

I have a history table which I would like to use to find the latest user in which updated specific items. Here is the query I have so far:

SELECT * 
  FROM  `history` 
WHERE  `pKey` 
  IN ( 13309, 13311, 13951, 14244, 1500, 15558, 15691, 15938, 9769 ) 
ORDER BY  `history`.`time` DESC 
LIMIT 0 , 30

This returns multiple history results for each pkey. Is there a way to limit the results to only the latest (based on time) entry from the specific pkey?

So for example: Right now pkey 13309 has multiple results returned. The query should only return the latest result for it. Same goes for 13311... etc.

Take a look at GROUP BY clause, i think it what you are looking for — Anthony Raymond, May 09 '14 at 21:42
@BillKarwin That question is for Oracle and the code here hints to MySQL; so the answer there probably won't help here — Lamak, May 09 '14 at 21:53
@Lamak, the top two answers for the question I linked to will run on MySQL. Also, I added the `greatest-n-per-group` tag to this question, which will lead to many other answers. This is a common question. — Bill Karwin, May 09 '14 at 23:00
@BillKarwin the accepted answer there won't work in MySQL actually, since it uses `OVER (PARTITION BY...)`, and windowing functions don't exist in MySQL — Lamak, May 09 '14 at 23:05
@Lamak, yes you're right, my mistake. Here's another `greatest-n-per-group` questions that is also specifically tagged `mysql`: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Bill Karwin, May 09 '14 at 23:26
@BillKarwin good find. I tried to vote to close the question but I'm using the android app and it seems rather difficult — Lamak, May 09 '14 at 23:51
I submitted a close vote on my question. I had no idea what to search for in order to find a solution to this. I just did not know what this kind of operation was called. — ComputerLocus, May 09 '14 at 23:56

Lamak · Accepted Answer · 2014-05-09T22:25:39.937

3

This should do:

SELECT h.*
FROM `history` as h
INNER JOIN (SELECT `pkey`, MAX(`time`) as MaxTime
            FROM `history`
            WHERE `pkey` IN (13309, 13311, 13951, 14244, 1500, 
                             15558, 15691, 15938, 9769)
            GROUP BY `pkey`) as t
    ON h.`pkey` = t.`pkey`
    AND h.`time` = t.`MaxTime`

edited May 09 '14 at 22:25

answered May 09 '14 at 21:51

Lamak

69,480
12
108
116

Getting an error: `#1054 - Unknown column 't.time' in 'on clause'` – ComputerLocus May 09 '14 at 22:24
@Fogest Yeah, I forgot that I renamed that column. Updated my answer now – Lamak May 09 '14 at 22:26

Hristo Ivanov · Answer 2 · 2014-05-09T22:01:00.957

0

this should work. Just grouping all the rows that have the same pkey. I think this will work. Comment with a feedback.

Select * from (
SELECT * 
  FROM  `history` 
WHERE  `pKey`
  IN ( 13309, 13311, 13951, 14244, 1500, 15558, 15691, 15938, 9769 ) 
ORDER BY  `history`.`time` DESC) as t1 group by pKey

edited May 09 '14 at 22:01

answered May 09 '14 at 21:42

Hristo Ivanov

679
8
25

Minus 1. In almost every rdbms out there, this will throw an error for having a group by clause without an aggregate. Also, the query is presented as prose instead of code. – Dan Bracuk May 09 '14 at 21:47
@DanBracuk Though I agree that it will return an error in almost every RDBMS, the code in the question looks like MySQL, where it won't throw an error (I mean, for what I know, I'm confused if it allows the `GROUP BY`after the `ORDER BY`) – Lamak May 09 '14 at 21:52
+1 (only because I think the downvote is unfair) . . . @DanBracuk, there is no database tag, but the use of backticks strongly suggests MySQL where this syntax is allowed. Note that this method of fetching rows is questionable because of MySQL documentation about *arbitrary* results being returned from non-aggregated columns in a `group by`. So, I don't like the solution particularly. – Gordon Linoff May 09 '14 at 22:34

Limit SQL Results to one result per pkey searched using "in"

2 Answers2