Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2011
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Exclamation Can't use select distinct properly!! Help!!

    Hi Humans! I'm just a damned right novice and I can't even use sql properly. So, some help would be appreciated. How do I select all the values from a database, bit like this: selecting all columns from a table where only I want distinct values for one column only. I've got:
    Code:
    SELECT DISTINCT test_username FROM test_records WHERE test_id = 'defaultvalue' ORDER BY test_id DESC LIMIT 5;
    Thanks, any help would be appreciated.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,722
    Thanks
    25
    Thanked 662 Times in 661 Posts
    Get rid of the DISTINCT.

    Code:
    SELECT test_username FROM test_records WHERE test_id = 'defaultvalue' ORDER BY test_id DESC LIMIT 5;

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    when you want distinct values from one column then you have to decide which row you want when there are multiple occurrences of that value from that column.

    As an example:
    City, time, temp
    Niagara, 2 p.m., 75
    Niagara, 3 p.m., 80
    Buffalo, 2 p.m., 81
    Chicago, 1 p.m., 92

    which of those two rows do you want for Niagara?

    DISTINCT is not a function, it works across all columns from your table, not on a single column. So all rows in the example are DISTINCT from one another.


  •  

    Posting Permissions

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