CODEWITHSUNDEEP
sqlsql-server
El.K Ibrahim
El.K Ibrahim

Reputation: 53

SQL where tuple in list

I need help in a query, i need to select for each year the name of the courses with the logest duration. My query looks like this 

select * 
from courses
where (cyear,duration) IN (
                          select cyear, max(duration)
                          from courses
                          group by cyear)

the problem with that is it's rise this following error at : (cyear,

Expression de type non booléen spécifiée dans un contexte où une condition est attendue, près de ','.

Upvotes: 0

Views: 2459

Answers (3)

paparazzo
paparazzo

Reputation: 45096

Using windows function 

select * 
from ( select * 
            , DENSE_RANK( ) over (partition by cyear order by duration desc) as rn
     ) tt
 where tt.rn = 1

Upvotes: 1

Thom A
Thom A

Reputation: 95830

Alternatively, you could go for an EXISTS:

SELECT *
FROM courses c
WHERE EXISTS (SELECT 1
              FROM cources e
              WHERE e.cyear = c.cyear
              HAVING MAX(e.duration) = c.duration);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

SQL Server doesn't support tuples with in. The closest reasonable syntax is to switch to a correlated subquery:

select c.* 
from courses c
where c.duration = (select max(c2.duration)
                    from courses c2
                    where c2.cyear = c.cyear
                   );

Upvotes: 2

Related Questions