CODEWITHSUNDEEP
sql-serversql-server-2016
william007
william007

Reputation: 18543

Selecting multiple columns in tables

I want to select guid and uuid appear in the inner table, but the following syntax is not allowed in SQL Server 2016, how should I do it?

select * 
from myTable
where (guid, uuid) in (select max(guid) as maxguid, convert(nvarchar(max), uuid) as uuid 
                       from myTable
                       group by convert(nvarchar(max), uuid) 
                      )

I have seen other answers like SQL WHERE.. IN clause multiple columns But I have a group by statement in my inner table, not sure how to use them.

Upvotes: 1

Views: 142

Answers (2)

Prisoner
Prisoner

Reputation: 1857

Put the grouping in inner-subquery and use exists for multiple columns

select * from myTable
where exists
(select 1
 from (select max(guid) as maxguid, convert(nvarchar(max), uuid) as uuid
       from myTable
       group by convert(nvarchar(max), uuid)) innerTable
 where myTable.guid = innerTable.maxguid
 and myTable.uuid = innerTable.uuid)

Upvotes: 2

Ian Kenney
Ian Kenney

Reputation: 6436

You can use a sub query and a join - something along the lines of 

select 
  myTable.* 
from 
  myTable join 
  (  
    select 
      max(guid) as maxguid, 
      convert(nvarchar(max), uuid) as uuid 
    from 
      myTable
    group by
      convert(nvarchar(max), uuid) 
  ) x
  on 
    x.maxguid = myTable.guid and 
    x.uuid = myTable.uuid

Upvotes: 1

Related Questions