Re: limit 5 iclouding also all the equals value with the 5th

On 10/7/2017 10:58 AM, Kostas Konstantinidis wrote:
> Hi all,
> trying to display the top 10 Greek men directors I use:
>
> SELECT
> `t_people`.`person` AS `person`,
> COUNT(`st_peoplefilms`.`ID_films`) AS `CountOfID_films`
> FROM
> (`t_people`
> JOIN `st_peoplefilms` ON ((`t_people`.`ID_person` = `st_peoplefilms`.`ID_person`)))
> GROUP BY `t_people`.`person` , `st_peoplefilms`.`ID_idiotita` , `kost36_greekfilm`.`t_people`.`an`
> HAVING ((`st_peoplefilms`.`ID_idiotita` = 29)
> AND (`t_people`.`man` = 1))
> ORDER BY COUNT(`st_peoplefilms`.`ID_films`) DESC
> LIMIT 5
>
>
> What I need is to display also all the equal with the 5th records
>
>
> e.g.
> person CountOfID_films
> 1 100
> 2 95
> 3 70
> 4 68
> 5 65
> -----------------
> 6 65
> 7 65
> 8 65
>
> Any idea how to make it to work?
> thank's
>
>
>

This one's not easy. I find it easier to break this down into multiple steps then put the whole thing together later.

What you need to do is to find the top 5 counts, not the top 5 directors. So start by just getting the counts of all Greek directors, in descending order.

Next, modify the query to get the 5th one with LIMIT 5,1. Given your data, you should get 65.

Now that you have that, build a new query which lists all directors with a count >= that value (65). From your data above, you should get 8 items.

Finally, put everything together and replace the count in your final query with a subselect from your previous query.

Hopefully this will help. It's how I work with complex queries.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================