[AccessD] select top and limit...

[jack drawbridge]

There is a sample (found via Google and *not*  tested by me) of MySQL
ranking query with ties at
http://fellowtuts.com/mysql/query-to-obtain-rank-function-in-mysql/

Hope it is useful.

On Thu, May 5, 2016 at 6:31 AM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> Another thought:
>
> Select <current query> where CountofID_Films >=
> (Select CountofID_Fims from <current query> LIMIT 10,1)
>
> On 5 May 2016 at 20:08, Stuart McLachlan wrote:
>
> > About the only way that I can see it would be
> > a UNION to a second query which return all the records matching the
> > last. Something like:
> >
> > Select <currentquery>  LIMIT 9
> > UNION
> > Select <currentquery>.
> > where CountOfID_films IN
> > <Select COuntofID_Films with currentQuery> LIMIT 10,1
> >
> >
> > On 5 May 2016 at 11:25, Kostas Konstantinidis wrote:
> >
> > > 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
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>