Select a distinct pair of fields?

[Switch0r]

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?

[obsidian]

can you not just do a [i]SELECT DISTINCT lastname, firstname FROM table[/i]?

seems like that should work for you.

[Switch0r]

doesn't that just select a distinct last name and a 'whatever' first name though?

i want a distinct first/last combo

[obsidian]

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]

[fenway]

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.

[Switch0r]

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 :)

[obsidian]

[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

[fenway]

[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.

[obsidian]

[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