Get a specific entry by group in SQL
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 :
mysql_query("SELECT * FROM icon WHERE tag='".$tag."' ORDER BY size LIMIT 0, 10");
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 defa开发者_如何学编程ult. Is this possible to tell SQL to send me only the 128 size when existing and if not another size ?
Thanks !
You need an additional column to order by that puts the preferred value at the top. For example, you could do something like this:
SELECT
*
FROM
icon
WHERE
tag='whatever'
ORDER BY
case size when 128 then 1 else 0 end desc,
size desc
LIMIT 0, 10
The new order by
clause puts your preferred size first (since it's the only one it assigns a 1 to), and then orders the rest of them by their actual size, biggest first.
ORDER BY size DESC
?
精彩评论