Avatar of RobertNZana
RobertNZanaFlag for United States of America asked on

How LIMIT MySql Query to top 10 rows - Error in SELECT clause: expression near '10'. Unable to parse query text.

Using latest versions of both ASP.NET and MySQL.  I want to create a query returning only the TOP 10 rows of data, but using LIMIT does not seem to work.  (This is a query within an XSD dataset.)

I've tried:
LIMIT 10
LIMIT 0,10
Putting the LIMIT after the WHERE and after the ORDER BY

The TableAdapter Query Configuration Wizard tells me:  Error in SELECT clause: expression near '10'. Unable to parse query text.

What am I missing?
SELECT     counties.County, states.UsStateAbbreviation, projects.ProjectId, projects.ProjectTitle
FROM         states INNER JOIN
                      counties ON states.StateId = counties.StateId INNER JOIN
                      projects ON counties.CountyId = projects.LocationCountyId
LIMIT 10
WHERE     (projects.IsVisible = 1) AND (projects.ReleaseDate <= CURDATE()) AND (projects.IsPrivate = 0)
ORDER BY projects.BidDateTime DESC

Open in new window

ASP.NETMySQL Server

Avatar of undefined
Last Comment
fabioce

8/22/2022 - Mon
Sharath S

Are you able to get all the records without LIMIT?  
SELECT     counties.County, states.UsStateAbbreviation, projects.ProjectId, projects.ProjectTitle
FROM         states INNER JOIN
                      counties ON states.StateId = counties.StateId INNER JOIN
                      projects ON counties.CountyId = projects.LocationCountyId
WHERE     (projects.IsVisible = 1) AND (projects.ReleaseDate <= CURDATE()) AND (projects.IsPrivate = 0)
ORDER BY projects.BidDateTime DESC

Open in new window

reb73

LIMIT has to be after the ORDER BY CLAUSE
Sharath S

>> Putting the LIMIT after the WHERE and after the ORDER BY
reb73 - yes, but i guess the asker tried that one also.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
reb73

My mistake! Apologies.. (Thanks, Sharath!)
ASKER
RobertNZana

Yes, I can get ALL records without the LIMIT.
SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RobertNZana

I tried...

SELECT * FROM projects LIMIT 10

In my TableAdapter Query Configuration Wizard (in my XSD dataset) I get this error....

Error in FROM clause: near '10'. Unable to parse query text.

I am using MySql with Corelabs MyDirect.Net MySql stuff...


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sharath S

that means, your MySql does not support LIMIT. I don't know the reason. Can you give a try with TOP 10 like
SELECT TOP 10....
ASKER
RobertNZana

Yeah I tried that too, but it didn't work.  I've been googling and see that MySql works with LIMIT.  In fact, when I use my MySql Admin tool I can use LIMIT.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
RobertNZana

I am using the connector from DevArt called MyDirect.Net.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.