Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select one row per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 3         | i hate this   |
| 6         | this is weird |
| 9         | how are you   |
| 16        | too slow      |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary? Is it possible to somehow use DISTINCT on a single column?

share|improve this question
up vote 5 down vote accepted

That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id values and for the rest of the columns, you can (have to) use aggregate functions like MIN(), MAX(), AVG(), SUM() as you will have more than one values per group and only one can be shown.

SELECT
    user_id
  , MIN(comment) AS comment  -- it will show the first in alphabetical order  
                             -- you could also use MAX()
FROM
    tableX
GROUP BY
    user_id ;

MySQL allows also the following unorthodox solution, that will return one (more or less random) comment per user:

SELECT
    user_id
  , comment
FROM
    tableX
GROUP BY
    user_id ;

This last query will not work but raise an error if the (stricter) ONLY_FULL_GROUP_BY mode is enabled. In the recently released 5.7 version, this mode is the default and a new function, ANY_VALUE(), is provided. For more details, see the MySQL Handling of GROUP BY page. The query can be written now:

SELECT
    user_id
  , ANY_VALUE(comment) AS comment
FROM
    tableX
GROUP BY
    user_id ;

Note that with either the "unorthodox" version or using the recent ANY_VALUE() function, if we add more columns in the SELECT list, their values is not guaranteed to be from the same row, just from a row in the same group. The way they are selected is not exactly random, depends on the execution plan and the indexes used.

share|improve this answer
    
Are there any other ways to specify which row gets pulled for a user_id? Any way to specify an ORDER BY kind of? – Jake Wilson Sep 25 '12 at 22:05
    
Besides MIN and MAX ? – ypercubeᵀᴹ Sep 25 '12 at 22:07
1  
Then it's more complex. See this other question: MySQL Query - How to get most recent demographics? – ypercubeᵀᴹ Sep 25 '12 at 22:20
2  
You'll also find a ton of similar problems in SO site, under the [greatest-n-per-group] tag. – ypercubeᵀᴹ Sep 25 '12 at 22:21
1  
@T.BrianJones do you mean in the "unorthodox" query, if you add all other columns in the SELECT list? It's the first, they may not be from the same row. It's not exactly random but the values can be from different rows (from the same group). – ypercubeᵀᴹ Dec 16 '15 at 6:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.