CODEWITHSUNDEEP
mysql
Jerald Lopez
Jerald Lopez

Reputation: 1

Selecting rows with unique field values in mysql

I have these columns for table comments:

id
content
add_date
uid
school_id

Rows can have the same school_id.

I want to select the latest data according to add_date, but only 1 row per school_id (no duplicate for school_id) with limit of 10.

I've tried many codes already and its not working for me. Any help would be appreciated.

Upvotes: 0

Views: 173

Answers (3)

Ambrose
Ambrose

Reputation: 511

This is what we call Greatest N per Group. You can achieved this by putting into a subquery so it can be joined against the non-grouped table (comments). Try this:

SELECT  c.*
FROM
        (
            SELECT school_id, MAX(add_date) maxDate
            FROM comments
            GROUP BY school_id
        ) x INNER JOIN comments c
            ON  x.school_id = c.school_ID AND
                x.maxDate = c.add_date
ORDER BY x.maxDate desc
LIMIT 10

Upvotes: 1

Justin Swanhart
Justin Swanhart

Reputation: 1856

select c1.*
  from comments c1
 where add_date = (select max(add_date) from comments c2 where c2.school_id =c1.school_id)
 order by add_date desc
 limit 10

create indexes on comments(add_date) and comments(school_id, add_date)

Upvotes: 0

Gonzalo.-
Gonzalo.-

Reputation: 12672

select C.ID, C.Content, t1.MaxDate as [add_date], C.uid, t1.school_id
from (selet school_id, max(add_Date) as 'MaxDate'
from comments
group by school_id) T1
inner join comments C on T1.school_id = C.school_id and C.add_Date= T1.MaxDate
LIMIT 10

If you want to choose which 10 rows return, add an order by, or a Where clause

Upvotes: 0

Related Questions