i've the below table.
i want to select all userid's where rank=1 and program='tennis' AND rank=2 and program='cricket' AND rank=3 and program='football'. As per the table data the result should be user2 and user3.
i tried the below but i doesn't seem to work. Can someone please help out.
select userid from table
where (rank=1 and program='tennis') and (rank=2 and program='cricket') and (rank=3 and program='football')
Use OR instead of AND between the conditions.
where (rank=1 and program='tennis') OR (...
There is no row in your table that satisfies. since a row can have only one rank and one program. try this instead:
...where (rank = 1 and program = 'tennis') or (rank = 2 and program = 'cricket') or (rank = 3 and program = 'football')
Hi, it doesn't seem to be working. i'm getting back all the users in the result. i want to get only user2 and user3 in the result since only for these 2 users it satisfies the condition which i'm looking for (ie, the rank=1 and program='tennis', rank=2 and program='cricket' and rank=3 and program='football', all 3 conditions satisfied together). I think with the OR in between it returns userid's satisfying each condition type individually.
For user1 if you see the data says, rank=1 and program='tennis', rank=2 and program='football' and rank=3 and program='cricket' which does not satisfy my condition.
Hi, With the OR in between it doesn't seem to work. i'm getting back all the users in the result. i want to get only user2 and user3 in the result since only for these 2 users it satisfies the condition which i'm looking for (ie, the rank=1 and program='tennis', rank=2 and program='cricket' and rank=3 and program='football', all 3 conditions satisfied together). I think with the OR in between it returns userid's satisfying each condition type individually.
For user1 if you see the data says, rank=1 and program='tennis', rank=2 and program='football' and rank=3 and program='cricket' which does not satisfy my condition.
Try
select userid
from table
where
case when rank=1 and program='tennis' then 1 else 0 end +
case when rank=2 and program='cricket' then 1 else 0 end +
case when rank=3 and program='football' then 1 else 0 end = 3
SELECT userid
FROM table_name
--just for efficiency, to ignore rows that don't match what we're checking for.
WHERE rank IN (1,2,3) AND program IN ('cricket','football','tennis')
GROUP BY userid
HAVING COUNT(*) = 3
/* if you ever need more specific checks, you can do something like this:
HAVING MAX(CASE WHEN rank=1 AND program='tennis' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rank=2 AND program='cricket' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rank=3 AND program='football' THEN 1 ELSE 0 END) = 1
*/
ORDER BY useridI wish you had said that at the begging!
Hi, could you please explain the working of the below. i'm a little new to using the case statement in the having clause
HAVING MAX(CASE WHEN rank=1 AND program='tennis' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rank=2 AND program='cricket' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rank=3 AND program='football' THEN 1 ELSE 0 END) = 1
Thanks
Sure.
"HAVING MAX(CASE WHEN rank=1 AND program='tennis' THEN 1 ELSE 0 END) = 1"
This will check every row for " rank=1 AND program='tennis' ". If it matches, the CASE will return 1, if not, it will return 0. Since we're using MAX(), if 1 ever gets returned, the final result will be 1, since 1 will always be max over 0. If no match is ever found, the final result will be 0. Therefore, we can check the result for 1 to see if the condition was ever met.
The result is for whatever column(s) we're GROUPing BY. That is the difference between WHERE and HAVING: WHERE looks at only the current row, but HAVING considers all rows the the GROUP.