[AccessD] select top and limit...

[Stuart McLachlan]

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
>