#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2009
    Posts
    45
    Rep Power
    47

    Use "Distinct" in SQL query to return more than one column


    I know this isn't a SQL forum, but it's amazing how thick those guys are. I did a search to find a sql query that would return distinct recordsets with more than one field. This query:

    Code:
    SELECT DISTINCT fieldname FROM tblname"
    only returns one field. I want it to return two fields, but:

    Code:
    SELECT DISTINCT fieldname1, fieldname2 FROM tblname"
    returns all recordsets.

    The people in the MySQL forums can't wrap their heads around what I'm looking for. They kept asking the other people who asked what exactly they wanted to do.

    Now, I don't need this query, it just came up because I was doing a PHP/Ajax tutorial and I wanted to grab data from the database instead of hard coding it. I used a database table that I was putting fake comments into, often by the same person more than once. So the table has multiple instances of the same names. I want to know if I can filter the name field to distinct names, but also include the "id" field in the results, since this PHP/Ajax tutorial uses the id field as the "value" in the dropdown list. Again, I don't desperately need this query, I'm just wondering if there's a way to do it, just in case.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,986
    Rep Power
    9397

    Moved from PHP because it's supposed to be here


    Code:
    SELECT DISTINCT fieldname1, fieldname2 FROM tblname
    does the distinct-ness on both of the fields at once, not on just fieldname1.

    What you're asking for doesn't make sense. What are the actual fields and how distinct do you want them?
  4. #3
  5. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,934
    Rep Power
    4033
    Originally Posted by mystic7
    I want to know if I can filter the name field to distinct names, but also include the "id" field in the results, since this PHP/Ajax tutorial uses the id field as the "value" in the dropdown list. Again, I don't desperately need this query, I'm just wondering if there's a way to do it, just in case.
    If the IDs are different even though the name is the same, then no DISTINCT will not help you. As mentioned it applies to all selected fields, not just the first.

    Your options then are to
    a) Choose either a MIN or MAX ID to reduce the set of IDs to one,
    b) Join them using GROUP_CONCAT, or
    c) Do a normal select with the duplicates, and handle it on your processing code.


    Options A/B involve using GROUP BY and one of the aggregate functions to create a single value from the list of IDs.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2009
    Posts
    45
    Rep Power
    47
    Originally Posted by requinix
    Code:
    SELECT DISTINCT fieldname1, fieldname2 FROM tblname
    does the distinct-ness on both of the fields at once, not on just fieldname1.

    What you're asking for doesn't make sense. What are the actual fields and how distinct do you want them?
    Let me try and make it as unconvoluted as possible.

    I have a table on my database where I store comments from a comment board I'm making. It has id, name, comment, email, date and time. There are multiple instances of the same name. If I do the usual sql query of:
    PHP Code:
    SELECT DISTINCT name FROM tblname 
    It works, naturally, but that query ONLY returns the field mentioned in it, in this case "name". I need "name" and "id" because the value of the name in the dropdown list is the associated id number. I tried making the value the same as the name that shows in the dropdown, but it doesn't work. Trying to mess with the ajax so that it looks for a name as a value, no good.

    Again, I don't need this, but I'm curious because I remember in the past having this dilemma. I don't need distinct values from two different fields. I need it from one, "name", but I also need to grab the corresponding id record.

    Here's the dropdown from the AJAX sample:
    Code:
    <form>
    <select name="users" onchange="showUser(this.value)">
    <option value="">Select a person:</option>
    <option value="1">Peter Griffin</option>
    <option value="2">Lois Griffin</option>
    <option value="3">Glenn Quagmire</option>
    <option value="4">Joseph Swanson</option>
    </select>
    </form>
    Notice it uses the id as the "value". Again, I'm doing it with a dynamically generated dropdown, so I need the corresponding "id" that goes with the DISTINCT "name". And your typical DISTINCT sql query only returns the recordsets in the field being queried.
    Last edited by mystic7; October 11th, 2013 at 01:49 PM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    The usual way this is done (if e.g. window functions are not available) is to join on a group by query:

    Code:
    select t1.id, t1.name, t1.some_other_column
    from the_table t1
      join (select max(id) as id, name
            group by name
            from the_table
      ) t2 on t1.id = t1.id
    Whether you use max(id) or min(id) doesn't matter, but you have to pick one by applying an aggregate function.

    In the select from the base table you can include any other column.

    If you do not need the other columns (so only id and name) then the inner select itself (the group by part) is all you need.
    Last edited by shammat; October 11th, 2013 at 01:52 PM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by mystic7
    I don't need distinct values from two different fields. I need it from one, "name", but I also need to grab the corresponding id record.
    okay, let me see if i can explain this

    since there are multiple rows with the same name, then there must be multple id values

    i'm assuming id is an auto_increment, right?

    so it's wrong to say "grab the corresponding id record"

    you need to say "grab a corresponding id record"

    now, let me ask you, does it matter which one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    In proper databases you can do:

    SELECT DISTINCT ON (name), id
    FROM table;

    Which will make the database return the first row found for every unique value of "name".

    This is roughly the same as

    SELECT name, MIN(id) AS id
    FROM table
    GROUP BY name;

    which selectes the lowest value of "id" for each unique value of "name"

    But, what the others have said before still stands; the id does not belong to the name, it belongs to a comment by that name.
    your dropdown would therefore not point to "the user named X" but to "the latests comment by a user name X".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by Vinny42
    In proper databases you can do:

    SELECT DISTINCT ON (name), id
    FROM table;
    That is a Postgres specific syntax - it's not standard SQL.

    And besides, your query has two major flaws:

    • using DISTINCT ON () without an ORDER BY is not recommended because it will not return consistent results.
    • your statement does not return the name column
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    That is a Postgres specific syntax - it's not standard SQL.
    True, other databases have slightly different syntaxes for it.
    Thanks for pointing that out.

    using DISTINCT ON () without an ORDER BY is not recommended because it will not return consistent results.
    Also true, but then again, the OP did not specify which of the ID's he wants.

    your statement does not return the name column
    Also true, I did not include the name field in the select. Bad, bad, evil me, must pay more attention next time.

    Anything else?

IMN logo majestic logo threadwatch logo seochat tools logo