CODEWITHSUNDEEP
mysqldatabase
slash197
slash197

Reputation: 9034

database query help wanted

Is it possible to limit the results a mysql select query returns with a condition?

For example I have a reviews table:

review_id, member_id, text, date

And I'd like to get the latest 10 reviews but member_id = 123 should only be taken once

Can this be achieved with a single query?

Upvotes: 0

Views: 48

Answers (1)

Matt Fenwick
Matt Fenwick

Reputation: 49105

My interpretation of the problem:

  • the 10 most recent reviews
  • including at most 1 review with member_id = 123

I'm going to solve this by:

  • starting with the full reviews result set
  • removing all reviews that have member_id = 123 except for the most recent one
  • from the modified result set, take the 10 most recent

Here's the query:

create view newest123 as ( -- this gets the newest review for member_id 123
  select * 
   from reviews 
   where member_id = 123 
   order by date desc limit 1
)

select * 
from (
  select * from newest123
  union
  select *                    -- all the reviews that aren't for member_id 123
    from reviews 
    where member_id != 123) filtered
order by date desc limit 10   -- sort 'em and take the top 10

Upvotes: 4

Related Questions