Take the 2-minute tour ×
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.

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

1 Answer 1

up vote 3 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 ;
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? –  Jakobud Sep 25 '12 at 22:05
    
Besides MIN and MAX ? –  ypercube Sep 25 '12 at 22:07
    
yes, pretend like perhaps there were other columns in the table and I wanted to grab the comment from the user that was first alphabetically and posted the nearest the NOW(). So sort of like ORDER BY multiple columns, but obviously using ORDER BY in this query would simply reorder the results, not actually manipulate which rows are SELECTED. –  Jakobud Sep 25 '12 at 22:13
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
1  
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

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.