CODEWITHSUNDEEP
mysqlsql
Ganesh R
Ganesh R

Reputation: 9

SQL Query - Search with SQL array results

Am trying this query to get result. but am not able to find any results. I don't know where i did the mistake.

Select * from loadcell_transaction_log where id = 

(SELECT  max(id) as id1
  FROM [addpro].[dbo].[loadcell_transaction_log] group by line2_count);

This is the error am getting:

Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 0

Views: 63

Answers (2)

Nikhil Shetkar
Nikhil Shetkar

Reputation: 346

using group by with max(id) will return max(id) for each line2_count :

select * from 
  (select log.*,row_number() over (partition by line2_count order by id desc ) rn FROM [addpro].[dbo].[loadcell_transaction_log] as log)
  where rn = 1;

If you want to check only for max(id) in the full table data then dont use group by clause else use in clause as referred by @farbiondriven.

Upvotes: 1

farbiondriven
farbiondriven

Reputation: 2468

Try this:

Select * from loadcell_transaction_log where id IN

(SELECT  max(id) as id1
  FROM [addpro].[dbo].[loadcell_transaction_log] group by line2_count);

I replace '=' with the keyword 'IN' which allows for multiple results in the subquery.

Upvotes: 2

Related Questions