Jump to content


Photo

Select a distinct pair of fields?


  • Please log in to reply
8 replies to this topic

#1 Switch0r

Switch0r
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts
  • LocationManchester, UK

Posted 10 October 2006 - 07:47 PM

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?
The Human Torch was denied a bank loan...

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 October 2006 - 08:01 PM

can you not just do a SELECT DISTINCT lastname, firstname FROM table?

seems like that should work for you.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 Switch0r

Switch0r
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts
  • LocationManchester, UK

Posted 10 October 2006 - 08:30 PM

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

i want a distinct first/last combo
The Human Torch was denied a bank loan...

#4 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 October 2006 - 08:54 PM

not according to the manual... the following two are identical queries. play around with it and see what you get
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 October 2006 - 09:04 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Switch0r

Switch0r
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts
  • LocationManchester, UK

Posted 10 October 2006 - 10:33 PM

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 :)
The Human Torch was denied a bank loan...

#7 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 11 October 2006 - 01:08 PM

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.


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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 October 2006 - 07:57 PM

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


Sorry about that, obsidian... I should have quoted this:

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


That's what triggered my response; your answer was entirely correct.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 11 October 2006 - 08:06 PM

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


ok... good to know ;) thanks
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users