1

My MySQL db has a table (cfg) with 4 fields: id, name, data, and location This one table is to service several subdomains (location). I need to get a list of the newest rows for each name for a given subdomain (location).

Example data

id   name   data   location
---------------------------
1    color  red    dc
2    color  blue   dc
3    size   large  sj
4    color  green  sj
5    size   small  dc

The following query works fine, but it seems too complicated and slow. Does anyone have a suggestion?

SELECT c1.name, c1.data
FROM (SELECT * FROM cfg WHERE location = "dc") as c1
LEFT JOIN (SELECT * FROM cfg WHERE location = "dc") as c2 ON ( c1.name = c2.name
AND c1.id < c2.id )
WHERE c2.id IS NULL

It would return

name     data
--------------
color    blue
size     small
goat
  • 31,486
  • 7
  • 73
  • 96
Nathan
  • 1,241
  • 3
  • 12
  • 17

3 Answers3

2

Use a subquery with GROUP BY that computes the max. id per name:

SELECT cfg.*
FROM   cfg
JOIN  (
    SELECT max(id) AS max_id
    FROM   cfg c0
    WHERE  location = 'dc'
    GROUP  BY name
    ) n ON n.max_id = cfg.id
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

I think

select c.name, c.data
from cfg c
join (
 select max(id) id
 from cfg
 group by name
 where location = 'dc'
) nameMaximums on nameMaximums.id = c.id

and use an index on (location, name, id) and one on just (id)

goat
  • 31,486
  • 7
  • 73
  • 96
0
select name, data
from cfg
where id in (select max(id) from cfg where location = "dc" group by name)

That's just untested freehand, but I think you get the idea. This presumes the column id is unique.

cschneid
  • 10,237
  • 1
  • 28
  • 39