SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Select a distint row, where can't use DISTINCT command

    The following query works for my purposes, but I want to return only one entry per cse_name. I can do that by removing cse_id
    and using DISTINCT, but that creates the bigger problem of having to get the cse_id later. So, can anyone tell me if I can change this query in any other way to get just one row per cse_name. I hope this is clear. In any event, thanks for your suggestions

    "SELECT cse_id, cse_name, cse_address, cse_city, cse_state, cse_zip FROM CAUSES WHERE
    cse_state = '$cse_state' AND (cse_name LIKE '%$keyword%' OR cse_keyword_one LIKE '%$keyword%'
    OR cse_keyword_two LIKE '%$keyword%' OR cse_focus LIKE '%$keyword%' OR cse_focus = '$cse_focus')";

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the problem lies in the relationship between cse_id and cse_name

    i'll assume cse_id is unique

    so it would appear that you have multiple rows (i.e. multiple cse_ids) with the same cse_name

    so, given these facts, which cse_id would you like to print alongside the distinct cse_name? the lowest? the highest? any random id?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Clarification

    Sorry my post was so vague, but I think you figured out my meaning. Yes, in the table the same cse_name appears several times, each time with a unique cse_id. I want to select the lowest cse_id.

    Thanks in advance.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, this is going to get messy, and it's not your fault, it's your data's fault

    if several cse_id's all have the same cse_name, then those rows form a group

    so what you need to do is
    Code:
    select cse_name, min(cse_id)
      from yourtable
     where conditions
     group by cse_name
    now, if you also want address, city, zip, etc., then you are actually dealing with different groups

    you could modify the above query slightly,
    Code:
    select cse_name, cse_address, cse_city, min(cse_id)
      from yourtable
     where conditions
     group by cse_name, cse_address, cse_city
    but then you will discover that what you thought was one group all with the same name are actually several groups, all with the same name but different addresses, etc.

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up THANX

    Thanx for the help. What you suggested works perfectly!

  6. #6
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Oops: How can i access cse_id with php?



    As I said, your suggestion worked perfectly...but that was when I tested it in the mysql monitor supplied by my web host. When I try it from my page's php script, I can get all values except the cse_id.

    Here's the relevant part of the script:

    $result = @mysql_query($sql, $db_cnx);
    $num_rows = @mysql_num_rows($result);

    if($num_rows != 0){
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    echo "$row[cse_id]";
    echo "$row[cse_name]";
    echo "$row[cse_address]";
    echo "$row[cse_city]";
    echo "$row[cse_state]";
    echo "$row[cse_zip]";
    }
    }

    Now, if I slip in echo $cse_id, I get nothing; if echo $MIN(cse_id), "cse_id" displays; if echo $row[MIN(cse_id)], I get a parse error message. Any ideas?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you need to assign a column alias to min(cse_id)

    select select cse_name, cse_address, cse_city
    , min(cse_id) as min_cse_id

    and then use that name in your php

    rudy

  8. #8
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Thanks and Another Option

    Thanks again rudy, that works well. While I was waiting for your answwer, I also figured out another way to do it, which any readers of this thread might want to see. That alternative is to change the php from am associative array to a numerical array as in the following.

    while($col = mysql_fetch_row($result)){
    echo "$col[0]";
    echo "$col[2]";
    etc.
    }

    I'm a php newbie with only the vaguest idea why this works, but it does.

    Thanks again for all your help.
    Frank

  9. #9
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Thanks and Another Option

    Originally posted by fitzgerf
    That alternative is to change the php from am associative array to a numerical array as in the following.

    while($col = mysql_fetch_row($result)){
    echo "$col[0]";
    echo "$col[2]";
    etc.
    }

    I'm a php newbie with only the vaguest idea why this works, but it does.
    that's because fetching it numerically means you don't have to alias the field name to one that makes sense to PHP... you had to alias 'min(cse_id)' because PHP can't have 'min(cse_id)' as an associative array's index (as in $row['min(cse_id)']) so you had to alias it to a legal name


Bookmarks

Posting Permissions

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