[AccessD] select top and limit...

[Kostas Konstantinidis]

Hi Paul thank's for your reply
This is the query which works but it doesn't return the equal values  with
the  10th record

    SELECT 
        `T_people`.`person` AS `person`,
        COUNT(`ST_peoplefilms`.`ID_films`) AS `CountOfID_films`
    FROM
        ((`MT_films`
        JOIN `ST_peoplefilms` ON ((`MT_films`.`ID_films` =
`ST_peoplefilms`.`ID_films`)))
        JOIN `T_people` ON ((`T_people`.`ID_person` =
`ST_peoplefilms`.`ID_person`)))
    GROUP BY `T_people`.`person` , `T_people`.`man` ,
`ST_peoplefilms`.`ID_idiotita` , `MT_films`.`kind_movie`
    HAVING ((`T_people`.`man` = 1)
        AND (`MT_films`.`kind_movie` = 'μ.Μ')
        AND ((`ST_peoplefilms`.`ID_idiotita` = 2)
        OR (`ST_peoplefilms`.`ID_idiotita` = 13)
        OR (`ST_peoplefilms`.`ID_idiotita` = 40)
        OR (`ST_peoplefilms`.`ID_idiotita` = 44)
        OR (`ST_peoplefilms`.`ID_idiotita` = 45)))
    ORDER BY COUNT(`ST_peoplefilms`.`ID_films`) DESC
    LIMIT 10

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Paul Hartland
Sent: Thursday, May 5, 2016 11:07 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] select top and limit...

never used mysql and know nothing about this limit 10 (although just quickly
looked it up), but if the values in the query you are setting the limit on
are ascending, could you not use the limit 10 as a sub query like
below...off top of head something like below and assuming the table/query
has some sort of unique identifier on

SELECT *
FROM [yourtableorqueryhere] AS A
INNER JOIN
    (
        SELECT UniqueID, MAX([yourvaluefieldhere]) AS GetRecords
    ) AS B
ON
    A.UniqueID = B.UniqueID
OR
    A.[yourvaluefieldhere] <= B.GetRecords




On 5 May 2016 at 08:46, Kostas Konstantinidis <kost36 at otenet.gr> wrote:

> Hi all,
> I uae "LIMIT 10" in mysql query but what I really need is get also all 
> the equal values with the 10th record I know that ms access do it by 
> default with "select top" but I can't  make it to work in mysql Could 
> you please help?
>
> Thank's
> /kostas
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com