How do I get distinct rows by a column?

Question

I have a huge number of rows that I'd like to get say, last 5 records inserted in that database from 10 different users. If the same user inserted the last 3 rows into database, we must get one row, skip the others two and move to get a row per user, until it count up to 5.

A database like that:

user_id | news_id | title
1       | 1       | foo-1
2       | 2       | foo-2
3       | 3       | foo-3
1       | 4       | baa
4       | 5       | baa0
5       | 6       | baa1
5       | 7       | baa2
6       | 8       | baa3
7       | 9       | baa4

Should return:

user_id | news_id | title
1       | 1       | foo-1
2       | 2       | foo-2
3       | 3       | foo-3
4       | 5       | baa0
5       | 6       | baa1

The current filter was done by PHP, like this:

$used = array();
while ($data = mysql_fetch_array($query)) {
   $uid = $data['user_id'];
    if(in_array($uid, $used))
        continue;
    array_push($used, $uid);

// do something with data
}

But I want to refactor it, and do the filter purely by mysql, if possible. I don't know much MySql and that's why I'm having problem to archive this...

Here's what I've tried

select DISTINCT(user_id), news_id, title from XXX
WHERE GROUP BY (news_id) DESC
LIMIT 0,5

How can I do that?

`WHERE GROUP BY` that would have thrown you an error `mysql_error()`. Read the manual on SELECT http://dev.mysql.com/doc/refman/5.7/en/select.html - you're also missing a `FROM table`. — Funk Forty Niner, Nov 16 '16 at 16:39
Please don't use the `mysql_*` functions anymore. They were deprecated in PHP 5.5., which is so old it no longer receives security updates, and completely removed in PHP 7. Use PDO or `mysqli_*` instead. See https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php for details. — ChrisGPT was on strike, Nov 16 '16 at 16:42
@aynber: Without the where it works but the issue is.. i have user_id with value of 0 in the database (it wasn't inserted by me, I'm just implementing new routines on that web site) that I also need to filter. How can I remove them? — Jack, Nov 16 '16 at 16:45
@Fred-ii-: I'm sorry, I edited the code here on OS and missed the `from` keyword. Edited — Jack, Nov 16 '16 at 16:45
@Chris: This is part of a old code base that I'm just doing maintenance, I'll edit this, at least not by now, but thanks anyway — Jack, Nov 16 '16 at 16:46
what you seem to need would be a `COUNT()` with HAVING > x_count - Here's a quick answer http://stackoverflow.com/a/7857565/ I pulled from Google and http://www.mysqltutorial.org/mysql-having.aspx and possibly iterate over results till it hits x-count. `If(count==x)`. I'm sort of thinking outloud here of course ;-) — Funk Forty Niner, Nov 16 '16 at 16:49
If you need to not have user_id of 0, then use the where `WHERE user_id != 0`. You only need the where if you're actually putting in restrictions. — aynber, Nov 16 '16 at 16:49

Matt · Accepted Answer · 2016-11-16T18:42:52.473

1

1 way you can do it is to generate a partitioned row number per user and then select 5 records where RowNumber = 1.

SELECT *
FROM
  (
    SELECT
      d.user_id
      ,d.news_id
      ,d.title
      ,(@rn:= if(@uid = user_id, @rn + 1,
                 if(@uid:=user_id,1,1)
                 )
        ) as RowNumber
    FROM
      Data d
      CROSS JOIN (SELECT @uid:=-1, @rn:=0) vars
    ORDER BY
      user_id
      ,news_id
) t
WHERE
  t.RowNumber = 1
ORDER BY news_id
LIMIT 5;

http://rextester.com/JRIZI7402 - example to show it working

Note you can change the row order by simply changing the ORDER BY statement of the derived table so if you have a column that will signify the latest record e.g. an identity column or a datetime column you can use that, but user_id must be the first criteria to be partitioned correctly.

edited Nov 16 '16 at 18:42

answered Nov 16 '16 at 16:54

Matt

13,833
2
16
28

Amazing answer! I have just one more question, I'm trying to change the rows order so that the lastest news are returned (and the the oldest ones) for example, news of id `107, 109, 112` etc are returned where the news id of the lastest ones are like `4003, 4002, 4001, 400` etc How can I change the `ORDER BY` to return those rows? I've tried filter by the date column but it's of string type (varchar actually) and not datetime (it was not designed by me, I'm just doing maintenance on this)... – Jack Nov 16 '16 at 17:49
@Jack thanks to reverse the order of news_id to get latest first simply add DESC for descending after ,news_id in the ORDER BY. To use the varchar date column you can use STR_TO_DATE() http://stackoverflow.com/questions/15396058/how-to-convert-varchar-to-datetime-format-in-mysql If my answer helped solve this for you please up vote and accept so that others know you have what you need thanks. – Matt Nov 16 '16 at 17:59
@Jack ORDER BY user_id, news_id DESC – Matt Nov 16 '16 at 18:00
Will this `ORDER BY user_id ,news_id , STR_TO_DATE(...)` put the lastest news on top? – Jack Nov 16 '16 at 18:00
@Jack problably this ORDER BY user_id, STR_TO_DATE(...) DESC – Matt Nov 16 '16 at 18:01
How can I do that without date column, instead of using `news_id` with highest values? `ORDER BY user_id ,news_id DESC` didn't worked for me and returned same value as `ORDER BY user_id ,news_id` – Jack Nov 16 '16 at 18:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128287/discussion-between-matt-and-jack). – Matt Nov 16 '16 at 18:15

score 0 · Answer 2 · answered Nov 16 '16 at 16:39

0

Do it from your query.

"SELECT * FROM table GROUP BY user_id ORDER BY news_id DESC LIMIT 5"

answered Nov 16 '16 at 16:39

Amir

98
1
1
5

score -1 · Answer 3 · answered Nov 16 '16 at 16:40

-1

well, i think this will achieve what you are after.

select user_id, news_id, title from tableName
   GROUP BY user_id 
   ORDER BY news_id DESC
   LIMIT 0,5

Hope this helps!

answered Nov 16 '16 at 16:40

Ralph Thomas Hopper

733
4
15

Welcome to Stack Overflow! While this code snippet may solve the problem, it doesn't explain why or how it answers the question. Please [include an explanation for your code](//meta.stackexchange.com/q/114762/269535), as that really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. **Flaggers / reviewers:** [For code-only answers such as this one, downvote, don't delete!](//meta.stackoverflow.com/a/260413/2747593) – Scott Weldon Nov 17 '16 at 18:44

How do I get distinct rows by a column?

3 Answers3