CODEWITHSUNDEEP
mysqlsqlsql-server
Med Anis
Med Anis

Reputation: 297

Select From one table : single-row subquery returns more than one row

I'm trying to select some data from one table using a subquery, the main objectif is to select the last occurrence of each ID (the last ligne = the max NULIGN), so I used: 

SELECT * FROM TABLE1 WHERE NULIGN= (SELECT DISTINCT MAX(NULIGN) FROM TABLE1 GROUP BY ID);

I got the following error:

single-row subquery returns more than one row.

So how can I display all the occurrence with the max ligne number?

Upvotes: 0

Views: 1080

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

As alternative you could use join 

SELECT a.* 
FROM TABLE1 a
JOIN( SELECT ID,MAX(NULIGN) MAX_NULIGN
      FROM TABLE1 
      GROUP BY ID
) b
ON a.ID = b.ID
AND a.NULIGN= b.MAX_NULIGN;

Or make your sub query as correlated sub query

SELECT *
FROM TABLE1 a
WHERE a.NULIGN= (SELECT MAX(NULIGN) 
                 FROM TABLE1 
                WHERE ID = a.ID)

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Change the = and use IN operator

SELECT * FROM TABLE1 WHERE NULIGN IN (SELECT DISTINCT MAX(NULIGN) FROM TABLE1 GROUP BY ID);

Because when you use =, the operator is expecting to only 1 value on the RHS. So if you want to give a group of values, Use IN

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

Your subsquery return more than a row (based on group by id) so you need a IN clause 

SELECT * FROM TABLE1 WHERE NULIGN IN  (
    SELECT DISTINCT MAX(NULIGN) 
    FROM TABLE1 GROUP BY ID);

or you can do the same with a join 

SELECT * FROM TABLE1 
INNER JOIN (SELECT DISTINCT MAX(NULIGN) max_nulign
      FROM TABLE1 GROUP BY ID) T ON T.max_nulign = TABLE1.NULIGN ;

Upvotes: 1

Related Questions