CODEWITHSUNDEEP
sql
Tony
Tony

Reputation: 1057

My sql code has error

SQL code:

select DeviceUID, LogType
from `DeviceUsage_Table`
where DeviceUID in (select DeviceUID, Max(LogTime) 
                    from `DeviceUsage_Table` 
                    group by DeviceUID);

Error:

Operand should contain 1 column(s)

How do I solve it?

I want to use the first select result as new condition.

select DeviceUID, Max(LogTime) 
from `DeviceUsage_Table` 
group by DeviceUID

This code I can get each DeviceUID and its last LogTime.

I use the query result to select DeviceUID's LogType when it is at last LogTime, so my method is as above.

Upvotes: 0

Views: 42

Answers (4)

cbeeson
cbeeson

Reputation: 76

Your sub-query:

"SELECT DeviceUID, Max(LogTime) FROM `DeviceUsage_Table` group by DeviceUID"

currently returns two columns, DeviceUID and Max(Logtime). This won't work with DeviceUID in as following the in you need a list. Either a hand-written list: ("A", "B", "C") or a query that returns just one column.

What was your reasoning for returning Max(LogTime) too? Then I may be able to help more.

Upvotes: 2

Glorfindel
Glorfindel

Reputation: 22651

Well,

(SELECT DeviceUID, Max(LogTime) FROM `DeviceUsage_Table` group by DeviceUID)

returns two columns, so you can't use it in an IN clause. How to solve it depends on what you want with the query.

select DeviceUID, LogType
  from `DeviceUsage_Table`
  where DeviceUID in (SELECT DeviceUID FROM `DeviceUsage_Table` group by DeviceUID);

would be valid but does not make sense, as it is the same as

select DeviceUID, LogType
  from `DeviceUsage_Table`
  where DeviceUID

Upvotes: 0

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

You are selecting two columns in the subquery. You should have only one since you are comparing with one column. Try the below query

select DeviceUID, LogType from `DeviceUsage_Table`
        where DeviceUID in 
              (SELECT DeviceUID FROM `DeviceUsage_Table` group by DeviceUID);

Upvotes: 0

V-R
V-R

Reputation: 1401

The error message is explicit, it's because by the rules of SQL, in order to use the "in" keyword, the following SELECT must return only 1 column. So you might want to do something like : DeviceUID in (SELECT DeviceUID FROM ...

Upvotes: 0

Related Questions