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

On 07/10/17 15:58, 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
>
>
>
I have always found mysql gets very clunky on complex queries, but I suspect you could JOIN the basic limit 5 enquiry with another inquiry based on records equalling the score of the 5th ranked person.

When I had occasion to do some work on a postcode database of millions of records, it turned out to be far far easier to do the complexity in te application code: the single SQL query based on the results of a pervious one was HORRENDOUSLY slow.

so I did a query, looped through the result and did lots of smaller queries to assemble my result dataset.

Which is a way of sating why I am not going to offer an SQL based solution.

Frankly the depths of the SQL language are as impenetrable as REGEXPs and I oeersonally find teht I have always been able to code in a a language I am more fmailor in in less than te stime it takes to work out what te REGEXP or the SQL statement should be, and the damn thing usually runs faster, because unlike SQLD, I know exactly how to optimise for my special case.

Long gone are they days when low grade report writers sat at IBM terminals with no permissions to run anything but SQL langauge constructs. Or whatever that old report language was. RPG?

-- 
There’s a mighty big difference between good, sound reasons and reasons 
that sound good.

Burton Hillis (William Vaughn, American columnist)