Distinct question

Question

I have a table with two fields; firstname & lastname

I want to select Distinct lastnames but display both the first & last name in the results...for instance:

'SELECT DISTINCT lastname FROM people

will return just the lastname. How can I also display the firstname?

Answer 1

Let's say that you have this data:

LastName   FirstName
Jones      Bob
Jones      Dave

Do you want to display both "Bob Jones" and "Dave Jones"? Then you would do:

SELECT DISTINCT FirstName, LastName FROM people

If that's not what you want, how do you determine which first name to use?

Answer 2

SQL doesn't generally respond well to ambiguity. If you want to include only one instance of a last name, but any one corresponding first name, you'll have to describe how to choose which first name (even if you don't really care). Perhaps:

SELECT MIN(firstname), lastname
FROM ...
GROUP BY lastname

Answer 3

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

Taken from:

http://www.w3schools.com/php/php_mysql_select.asp

Very simple but good tutorial and I think applies perfectly!

Answer 4

Considering you are using MySQL, this query will work and return a "random" first name (it might always return the same for a while, then change after an update / delete / insert into query) :

SELECT FirstName, LastName FROM people GROUP BY LastName

Please note this query will not work on any intelligent RDMBS (i.e. not MySQL). A good way to do this (and actually pick random first names) would be by using analytical functions (PostgreSQL syntax) :

WITH TT AS (
    SELECT FirstName, LastName, ROW_NUMBER() OVER(PARTITION BY LastName ORDER BY random()) AS R
    FROM people
)
SELECT FirstName, LastName
FROM TT
WHERE R = 1;

Unfortunately for you, MySQL doesn't know CTE and analytical functions and makes it much harder to randomize things like this.