Syntax error in SQL subquery

Question

I am getting a syntax error can anybody tell me why?

SELECT  c.clientid, c.clientname, c.billingdate, 
      (SELECT TOP 1 previousbalance FROM invoice i 
          WHERE i.client = c.clientid ORDER BY i.invoiceid DESC) AS remaining 
FROM client c 
ORDER BY clientname

What the secondary select is doing is getting the latest record for that clientid in the invoice table.

The program - HediSQl

SQL

And here is the error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 previousbalance FROM invoice i WHERE i.client = c.clientid ORDER BY i.invoicei' at line 1 */

Please show the syntax error here. and which SQL (MySQL, SQL Server etc)? — Himanshu
– Himanshu, Commented Aug 29, 2012 at 5:24
HediSQL is the program, SQL and here is the error SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 previousbalance FROM invoice i WHERE i.client = c.clientid ORDER BY i.invoicei' at line 1 */ — user1269625
– user1269625, Commented Aug 29, 2012 at 5:29

edze · Accepted Answer · 2012-08-29 05:36:26Z

3

Just guessing but it might indicate that you should replace TOP 1 with LIMIT 1 or WHERE ROWNUM < 2 LIMIT 1. What kind of DB are you using?

edited Aug 29, 2012 at 5:36

edze

3,0331 gold badge25 silver badges29 bronze badges

answered Aug 29, 2012 at 5:32

tbl

7713 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tbl Over a year ago

This does not work?

SELECT  c.clientid, c.clientname, c.billingdate, (SELECT previousbalance FROM invoice i WHERE i.client = c.clientid ORDER BY i.invoiceid DESC LIMIT 1) AS remaining FROM client c ORDER BY clientname

tbl Over a year ago

This should also work:

SELECT c.clientid, c.clientname, c.billingdate, remaining.previousbalance FROM client c LEFT JOIN (SELECT i.client, previousbalance FROM invoice i GROUP BY i.client HAVING MAX(invoiceid)) remaining ON c.clientid = remaining.client ORDER BY c.clientname

. I also set up an SQLFiddle: sqlfiddle.com/#!2/29e80/10

Himanshu · Accepted Answer · 2012-08-29 05:39:25Z

1

You just need to use LIMIT instead TOP like this:

SELECT  c.clientid, c.clientname, c.billingdate, 
   (SELECT previousbalance FROM invoice i 
     WHERE i.client = c.clientid ORDER BY i.invoiceid DESC LIMIT 1) AS remaining 
FROM client c 
ORDER BY clientname

See this SQLFiddle

answered Aug 29, 2012 at 5:39

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Comments

Mikael Eriksson · Accepted Answer · 2012-08-29 05:34:47Z

0

The syntax you have will work in SQL Server. Your error message is from MySQL.

Try this:

SELECT c.clientid, 
       c.clientname, 
       c.billingdate, 
       (
       SELECT previousbalance 
       FROM invoice i 
       WHERE i.client = c.clientid 
       ORDER BY i.invoiceid DESC
       LIMIT 1  
       ) AS remaining 
FROM client c 
ORDER BY clientname

answered Aug 29, 2012 at 5:34

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Syntax error in SQL subquery

3 Answers 3

2 Comments

See this SQLFiddle

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related