[AccessD] select top and limit...

Paul Hartland paul.hartland at googlemail.com
Thu May 5 05:17:39 CDT 2016


Sorry, but without knowing too much about MySQL and not seeing returned
results against example of what you expect not sure I can be of much more
assistence, I am sure someone will be able to help, I now also realise I
may need to start investigating MySQL
On 5 May 2016 11:10, "Stuart McLachlan" <stuart at lexacorp.com.pg> 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


More information about the AccessD mailing list