SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Using DISTINCT and COALESCE together?

    Below is a query which gives me the Usernames of the Last-10 Visitors to a Member's Profile...
    Code SQL:
    	// FORMER QUERY
    	$q8 = "SELECT COALESCE(m.username,'Non-Member') AS username
    			FROM visitor_log AS v
    			LEFT OUTER JOIN member AS m
    			ON m.id = v.visitor_id
    			WHERE v.member_viewed_id=?
    			ORDER BY v.created_on DESC
    			LIMIT 10";

    It produces output like this...
    The last 10 Visitors on this page were...
    username4
    username4
    JohnDoe
    JohnDoe
    Non-Member
    Non-Member
    Non-Member
    Non-Member
    Non-Member
    Non-Member

    Is there a way to use DISTINCT so that I would instead get a list like this...
    The last 10 Visitors on this page were...
    username4
    JohnDoe
    Non-Member

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Is there a way to use DISTINCT so that ...
    why, yes, there is

    in fact, there's only one way, which is to place the DISTINCT keyword right after the SELECT keyword
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why, yes, there is

    in fact, there's only one way, which is to place the DISTINCT keyword right after the SELECT keyword
    I tried this and it didn't work...
    Code SQL:
    	// FORMER QUERY
    	$q8 = "SELECT DISTINCT(COALESCE(m.username,'Non-Member')) AS username
    			FROM visitor_log AS v
    			LEFT OUTER JOIN member AS m
    			ON m.id = v.visitor_id
    			WHERE v.member_viewed_id=?
    			ORDER BY v.created_on DESC
    			LIMIT 10";


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why are you putting parentheses after the DISTINCT keyword? DISTINCT is ~not~ a function

    please, debbie, look stuff up in the manual if you're not sure

    the manual is a lot more accessible and available than your friendly go-to free tech support forum
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why are you putting parentheses after the DISTINCT keyword? DISTINCT is ~not~ a function
    Because I obviously didn't know that...


    please, debbie, look stuff up in the manual if you're not sure

    the manual is a lot more accessible and available than your friendly go-to free tech support forum
    It probably WHINES a lot less too...

    (Note to Debbie... Only ask questions which r937 will feel are worthy of his time!!)


    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you're right, the manual doesn't complain how many times you use it

    your propensity to ask, and ask, and ask for free help is just sometimes so annoying, you know?

    please give me a suggestion for how i should gently advise you to RTFM before posting
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    here is a particularly relevant thread --

    http://www.sitepoint.com/forums/show...Anonymous-quot

    seems we have taught you how to use DISTINCT before, eh
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you're right, the manual doesn't complain how many times you use it
    I tried using DISTINCT several times last night to no avail, so I came here today to ask for help.

    If I knew what I was doing wrong, then I wouldn't have needed help.

    I made an honest effort here...


    your propensity to ask, and ask, and ask for free help is just sometimes so annoying, you know?
    But it is no where near as annoying as your perpetual whining that my Threads on SP don't meet your approval...


    please give me a suggestion for how i should gently advise you to RTFM before posting
    The MySQL Manual looks like it was written by English flunkies... (Could be one reason I don't use it often - as opposed to how I *do* look at the PHP Manual regularly.)

    It might also be the fact that no amount of searching I did pulled up anything about using DISTINCT and COALESCE together, so I came here instead...

    No one makes you read or respond to my threads, or is there a masked man - with gun in hand - forcing your hands to the keyboard, AGAIN??


    Debbie

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here is a particularly relevant thread --

    http://www.sitepoint.com/forums/show...Anonymous-quot

    seems we have taught you how to use DISTINCT before, eh
    I'm human, and sometimes forget things that I don't use often (e.g. DISTINCT).

    Where is the surprise there?


    Debbie


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
  •