ordering question [Archive]

padders

06-12-01, 21:04

Ok, i have some results in a table that look like

NAME | GROUP

header | fantasy
header |
footer |
footer | fantasy
main |

and I am doing a query to get them out. It is something like:

SELECT * FROM template
WHERE (name IN ('header','footer','main'))
AND (group_name='fantasy' OR group_name='')
ORDER BY group_name DESC LIMIT 0,3

Thing is, i get the two top rows fine but i want the 3rd row to be the one with a name that has not appeared yet if you see what i mean? like

header | fantasy
footer | fantasy
main |
footer |
header |

any ideas?

Paul

06-12-01, 21:41

here ya go Padders.

SELECT name, group_name FROM template
WHERE (name IN ('header','footer','main'))
AND (group_name='fantasy' OR group_name='')
ORDER BY group_name DESC,name DESC LIMIT 0,3;

+--------+-------------+
| name | group_name |
+--------+-------------+
| header | fantasy |
| footer | fantasy |
| main | |
+--------+-------------+

ps... welcome to dBforums :D
by the way, I reformatted your post coz you totally ballsed up my display! ;)

padders

06-12-01, 22:10

thank you for the welcome and for mending post, such lovely templates shame to ruin them :)

on the SQL, wow. That looks really counter intuitive to me, can you explain why it does that? does

ORDER by group_name DESC, name DESC not mean:

ORDER by group_name DESC and if you have two identical group_names then order them by name ?

if so then i would have expected different results so guess that is not what it means... so what does it :)

i had just worked out a php solution to this:

by getting rid of the LIMIT bit and doing

while ($template = $db->row_array()) {

// have to deal with multipe template results.
// Ensures that specific template is choosen
// in preference to non-specific by creating an array
// of templates that have been created.

if ($array[$template[name]] != "done") {
$template_cache[$template[name]] = $template[template];
$array[$template[name]] = "done";
}

but if i can do it all in SQL i am even happier :)

Paul

06-12-01, 22:16

Originally posted by padders
on the SQL, wow. That looks really counter intuitive to me, can you explain why it does that?
Nope :) I only know it works. I agree it would be more logical to work as you expected though.

padders

06-12-01, 22:36

Arrr... i worked it out, i thought this is what you had done but the DESC tricked me.

The reason it worked here is because it happens that the one that i wanted to appear 3rd happens to be the last in the alphabet of the 3. This might not always be the case, it is just a co-incidence. It might be that the header is the one with only header | and that needs to appear 3rd, so basically it was a quirk :)

I think a code solution is the best way, and i have found one so am happy :) cheers though, am learning a lot of SQL as i muck along here!