I've a database who contain some datas in that form:
icon(name, size, tag)
(myicon.png, 16, 'twitter')
(myicon.png, 32, 'twitter')
(myicon.png, 128, 'twitter')
(myicon.png, 256, 'twitter')
(anothericon.png, 32, 'facebook')
(anothericon.png, 128, 'facebook')
(anothericon.png, 256, 'facebook')
So as you see it, the name field is not uniq I can have multiple icons with the same name and they are separated with the size field. Now in PHP I have a query that get ONE icon set, for example :
$dbQueryIcons = mysql_query("SELECT * FROM pl_icon WHERE tag LIKE '%".$SEARCH_QUERY."%' GROUP BY name ORDER BY id DESC LIMIT ".$firstEntry.", ".$CONFIG['icon_per_page']."") or die(mysql_error());
With this example if $tag contain 'twitter' it will show ONLY the first SQL data entry with the tag 'twitter', so it will be :
(myicon.png, 16, 'twitter')
This is what I want, but I would prefer the '128' size by default. Is this possible to tell SQL to send me only the 128 size when existing and if not another size ?
In an another question someone give me a solution with the GROUP BY but in this case that don't run because we have a GROUP BY name. And if I delete the GROUP BY, it show me all size of the same icons.
Thanks !
Try this ORDER BY clause (id or size?):
ORDER BY case when size=128 then 999999 else size end DESC
Try using subqueries.
SELECT * FROM (
SELECT * FROM pl_icon
WHERE tag LIKE '%$SEARCH_QUERY%'
ORDER BY IF(size = 128, 0, 1)
) pl_icon
GROUP BY name
ORDER BY id DESC
LIMIT ...
We find all matching rows in the inner query and place rows with size = 128 above all others. Then, for each unique name we choose only the first record using GROUP BY.
If what you really want is get the record with the largest size then:
SELECT name, tag, MAX(size)
FROM pl_icon
WHERE tag LIKE '%".$SEARCH_QUERY."%'
GROUP BY name, tag
But what's confusing here is that the 'name' is not unique. Usually in such scenarios people want to identify the record where a maximum (or minimum) occurs - like the last meter reading, or biggest invoice. So if your table had an id field and you wanted to retrieve that:
SELECT pl2.*
FROM pl_icon pl2,
(SELECT name, tag, MAX(size) AS biggest
FROM pl_icon
WHERE tag LIKE '%".$SEARCH_QUERY."%'
GROUP BY name, tag) ilv
WHERE ilv.name=pl2.name
AND ilv.tag=pl2.tag
AND ilv.biggest=pl2.size;
However this can be rather inefficient for very large tables / selects - when you might want to try the MAX(CONCAT trick
C.