[SOLVED] Select 2 most recent for each distinct value

[Maq]

I am trying to select the two most recent (highest) meta_id's for each distinct catcode.  I can only seem to extract one.  I am using MySQL 4.1.20.

 

My current query:

SELECT meta_id, catcode 
FROM pda_dbs 
GROUP BY catcode 
ORDER BY meta_id DESC;

 

Table structure:

mysql> describe pda_dbs;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| meta_id | int(11) |      | PRI | NULL    | auto_increment |
| catcode | int(10) |      | MUL | 0       |                |
| version | int(10) |      |     | 0       |                |
| oncheck | int(11) |      |     | 0       |                |
+---------+---------+------+-----+---------+----------------+

 

TIA.

 

[kickstart]

Hi

 

Can't think of an elegant way to do it. This should do it but is pretty horrible:-

 

SELECT a.catcode, a.max_meta, b.almost_max_meta

FROM (SELECT catcode, MAX(meta_id) as max_meta FROM pda_dbs GROUP BY catcode) a

(SELECT catcode, MAX(meta_id) AS almost_max_meta FROM pda_dbs

WHERE meta_id NOT IN

LEFT OUTER JOIN (SELECT max_meta_id

FROM (SELECT catcode, MAX(meta_id) AS max_meta_id FROM pda_dbs GROUP BY catcode))

GROUP BY catcode) b

ON a.catcode = b.catcode

 

You could probably clean it up and remove the NOT IN.

 

All the best

 

Keith

[Maq]

I tried your query and received the following error.  It may be a version issue related to the sub-queries.  I can't really spot the correction for the error, any suggestions?

 

mysql> SELECT a.catcode, a.max_meta, b.almost_max_meta
    -> FROM (SELECT catcode, MAX(meta_id) as max_meta FROM pda_dbs GROUP BY catcode) a
    -> (SELECT catcode, MAX(meta_id) AS almost_max_meta FROM pda_dbs
    -> WHERE meta_id NOT IN
    -> LEFT OUTER JOIN (SELECT max_meta_id
    -> FROM (SELECT catcode, MAX(meta_id) AS max_meta_id FROM pda_dbs GROUP BY catcode))
    -> GROUP BY catcode) b
    -> ON a.catcode = b.catcode;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT catcode, MAX(meta_id) AS almost_max_meta FROM pda_dbs
WHERE meta_id NOT ' at line 3

 

[roopurt18]

Maybe:

select
  *
from pda_dbs a
where a.meta_id in (
  select b.meta_id from pda_dbs b where a.catcode=b.catcode
  order by b.meta_id desc limit 2
  )

[Maq]

Thanks for the reply roopurt, but I have actually already tried that query (sorry I forgot to mention it) which throws the following version error:

 

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[roopurt18]

Eh.  This might help then:

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_cid580284,00.html

(scroll to bottom)

 

Looks like you may need multiple queries and / or temp tables.

[kickstart]

Hi

 

Made a right mess of pasting that in a hurry didn't I.

 

Not tried it but I think this should work

 

SELECT a.catcode, a.max_meta, b.almost_max_meta
FROM (SELECT catcode, MAX(meta_id) as max_meta FROM pda_dbs GROUP BY catcode) a
LEFT OUTER JOIN (SELECT catcode, MAX(meta_id) AS almost_max_meta FROM pda_dbs 
WHERE meta_id NOT IN 
(SELECT max_meta_id 
FROM (SELECT catcode, MAX(meta_id) AS max_meta_id FROM pda_dbs GROUP BY catcode) Deriv1)) b
ON a.catcode = b.catcode

 

Basically doing 2 selects.

 

One subselect to get the max meta_id for each catcode.

 

The 2nd one is getting the max meta_id for each catcode that is IN a select of all the max meta_ids.

 

All the best

 

Keith