sql limiting for unique records

Question

I have a table that may have about 100 records or so, with a user name field. There may be many records with the same username. How would I ensure that only one record for each username is returned, regardless of which record it is?

e.g. for the following table

users

username    item firstname lastname
-------------------------------------------------
superhans   shoes super  hans
jez  hat jeremy  sisto
jez  book jeremy  sisto
flinto   train fred  flintstone
superhans   shirt super  hans

Only the following would be returned:

username    item firstname lastname
-------------------------------------------------
superhans   shoes super  hans
jez  hat jeremy  sisto
flinto   train fred  flintstone

Answer 1

select distinct username, item, firstname from users

Answer 2

First off, your table should be constructed and maintained such that this state does not occur. Ever.

Second, I think what you want is SELECT DISTINCT.

Answer 3

You could use DISTINCT if you did not want to return the item field in your results:

SELECT DISTINCT username, firstname, lastname FROM users

If you want to include the item field, you would have to do something like this:

SELECT t.username, 
   (SELECT TOP 1 item FROM users WHERE username = t.username) AS item,
   t.firstname,
   t.lastname
FROM (
  SELECT DISTINCT username, item, firstname, lastname
  FROM users
 ) AS t

Using a query like that doesn't make much sense. Could you be a little more specific about what you are trying to achieve?

Answer 4

It would be best if you store the username in a table called users and use an unique id field called user_id.

Then use user_id as a foriegn key to store data in tables related to user ... e.g purchases, user info etc.

Answer 5

Make the username your primary key - that way, you can ensure uniqueness

Answer 6

In MySQL 5 (which you didn't mention, but you've tagged this question mysql, so I'll assume) you can:

SELECT *
FROM users
GROUP BY username

And you'll get an arbitrary row back for each user

See Mysql Refman 11.11.3:

MySQL extends the use of GROUP BY so that you can use non-aggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause. [...] Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Actually, thinking about it, you might want to do

SELECT username, GROUP_CONCAT(DISTINCT item), whatever
FROM users
GROUP BY username

Which will give you a list of all their items. Depends why you wanted that arbitrary row in the first place.

-----N