Jump to content

Archived

This topic is now archived and is closed to further replies.

Switch0r

Select a distinct pair of fields?

Recommended Posts

Hey peeps, was wondering if anyone can help me. I'm trying to get a list of all distinct field pairs from a table i have (the table is listing artists in a cd collection if that makes any difference) and i have the first and second names that i want to get distinct values of.

essentially i want to select a distinct pair of first and last, rather than just distinct last, as there may be crossover of the 2 names.

eg:

jeff buckley
tim buckley

i want to select a distinct pair rather than just the last name

any ideas?

Share this post


Link to post
Share on other sites
can you not just do a [i]SELECT DISTINCT lastname, firstname FROM table[/i]?

seems like that should work for you.

Share this post


Link to post
Share on other sites
doesn't that just select a distinct last name and a 'whatever' first name though?

i want a distinct first/last combo

Share this post


Link to post
Share on other sites
not according to the manual... the following two are identical queries. play around with it and see what you get
[code]
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;
[/code]

Share this post


Link to post
Share on other sites
DISTINCT is _not_ a function!  It is a modifier to SELECT, not to any of the columns.  Generally, I use GROUP BY when I actually want to group things, and DISTINCT for uniqueness.

Share this post


Link to post
Share on other sites
right, well I've checked what i've already got, and it turns out that you are all more than correct, and i already had what i wanted anyway.

i feel sheepish, i'll baa all the way home now :)

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=111124.msg450175#msg450175 date=1160514295]
DISTINCT is _not_ a function!  It is a modifier to SELECT, not to any of the columns.  Generally, I use GROUP BY when I actually want to group things, and DISTINCT for uniqueness.
[/quote]

fenway, i think i'm lost here. did i say something to instigate this argument about it being a function? i hope i didn't imply that it was :P

Share this post


Link to post
Share on other sites
[quote author=obsidian link=topic=111124.msg450433#msg450433 date=1160572132]
fenway, i think i'm lost here. did i say something to instigate this argument about it being a function? i hope i didn't imply that it was :P
[/quote]

Sorry about that, obsidian... I should have quoted this:
[quote author=Switch0r link=topic=111124.msg450160#msg450160 date=1160512221]
doesn't that just select a distinct last name and a 'whatever' first name though?
[/quote]

That's what triggered my response; your answer was entirely correct.

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=111124.msg450604#msg450604 date=1160596632]
That's what triggered my response; your answer was entirely correct.
[/quote]

ok... good to know ;) thanks

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.