CODEWITHSUNDEEP
sqlmysqllimit
Joshxtothe4
Joshxtothe4

Reputation: 4183

sql limiting for unique records

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

Upvotes: 1

Views: 7707

Answers (6)

Srikar Doddi
Srikar Doddi

Reputation: 15599

SELECT DISTINCT username, item, firstname FROM users

Upvotes: 1

NickZoic
NickZoic

Reputation: 7835

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

Upvotes: 3

Jimmie R. Houts
Jimmie R. Houts

Reputation: 7818

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?

Upvotes: 6

Alex Taylor
Alex Taylor

Reputation: 7168

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

Upvotes: 0

Rick J
Rick J

Reputation: 2703

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.

Upvotes: 0

Kalium
Kalium

Reputation: 4682

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.

Upvotes: 1

Related Questions