[AccessD] select top and limit...

[Stuart McLachlan]

Relevan tto this thread?  Possibly. (But I think my solutions are simpler)

Useful?  Very!  I've just added it to my toolbox :)

Thanks for that one.



-- 
Stuart

On 5 May 2016 at 7:06, jack drawbridge wrote:

> 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
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>