newest record for each user

[sniperscope]

Hi

I have a database called news and there is records something like this

 

news_id | user id | news | news_date

1                3            aaa    2012/4/1

2                2            bbb    2012/4/2

3                5            ccc      2012/4/3

4                3            ddd    2012/4/4

5                3            eee    2012/4/5

6                4            ffff      2012/4/6

7                3            ggg    2012/4/7

8                5            hhh    2012/4/8

9                4            iii        2012/4/9

 

and i want to get only one record for each user

so result should be

 

1  4  iii      2012/4/9  lisa

2  5  hhh  2012/4/8  mike

3  3  ggg  2012/4/7  jason

4  2  bbb  2012/4/2  david

 

and this is my query

SELECT n.user_id, n.news, n.news_date, s.user_name, s.city

FROM news_event n

LEFT JOIN user_master s ON n.user_id = s.id

WHERE s.city LIKE '%sydney%'

AND s.is_activated = 'y'

GROUP BY n.user_id 

ORDER BY n.news_date DESC

 

I know solution is so simple but i lost my capable of thinking after 18 hours of non stop work.

 

Thanks for any help.

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

[sniperscope]

http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

I knew it.

I knew the answer is so easy.

 

Thank you for url. So i know the solution now and let me jump into bed.

You are great person.

 

Have great day

[sniperscope]

Okay

I've changed my query something like this

 

SELECT n.shop_id, n.news, MAX( n.news_date )

FROM news n

WHERE n.area = 'sydney'

GROUP BY n.user_id

ORDER BY n.news DESC

 

This time it takes MAX date but news is oldest one

 

This is how db holds the record

news_id | user id | news | news_date

1                3            aaa    2012/4/1

2                3            ddd    2012/4/4

3                3            eee    2012/4/5

4                3            ggg    2012/4/7

 

but above query gives me this

 

news_id | user id | news | news_date

1                3            aaa    2012/4/7

 

it should give me

 

news_id | user id | news | news_date

1                3            ggg    2012/4/7

[PFMaBiSmAd]

You are going to need to reread the information at the link I posted.

 

Just using MAX() will get you the maximum value for that single field. All the other values will be from the first row encountered in the group.

[sniperscope]

Dear PFMaBiSmAd

Thanks for help again.

 

I want to ask you something, if you check my posts in mysql forum that you can see i have problems with joins

Can you recommend some books and/or methods to learn joins.

 

Regards

[fenway]

I recently stickied a good resource.

[Barand]

Use subquery to find max date for each user then join with this to match users and dates

SELECT
    N.user_id
  , N.news
  , N.news_date
FROM news N
INNER JOIN
(
  SELECT user_id, MAX(news_date) as maxdate
  FROM news
  GROUP BY user_id
) as X

ON N.user_id = X.user_id AND N.news_date = X.maxdate
ORDER BY N.news_date DESC