Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2001
    Posts
    8

    Unanswered: ordering question

    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:

    Code:
    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?

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    here ya go Padders.

    Code:
    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
    by the way, I reformatted your post coz you totally ballsed up my display!

  3. #3
    Join Date
    Jun 2001
    Posts
    8
    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

    PHP Code:
    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

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    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.

  5. #5
    Join Date
    Jun 2001
    Posts
    8
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •