CODEWITHSUNDEEP
mysqlsyntax
user1269625
user1269625

Reputation: 3209

Syntax error in SQL subquery

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 */

Upvotes: 1

Views: 859

Answers (3)

Himanshu
Himanshu

Reputation: 32602

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

Upvotes: 1

tbl
tbl

Reputation: 771

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?

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 0

Related Questions