Using DISTINCT and COALESCE together?

Databases
1

Below is a query which gives me the Usernames of the Last-10 Visitors to a Member’s Profile…


	// 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

why, yes, there is :slight_smile:

in fact, there’s only one way, which is to place the DISTINCT keyword right after the SELECT keyword

3

I tried this and it didn’t work…


	// 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

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

5

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… :wink:

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

Debbie

6

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

7

here is a particularly relevant thread –

seems we have taught you how to use DISTINCT before, eh

8

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

Debbie

9

I’m human, and sometimes forget things that I don’t use often (e.g. DISTINCT).

Where is the surprise there?

Debbie