Forum Discussion
Tricky where clause
I have a very simple two attribute (User_integration_ID and Pick) relation and from this relation I am trying to select ONLY those attributes where the pick value is <=2 but if a particular user_integration_id has two rows, one with a pick value>2 a value <=2 I would want to exclude that user_integration_ID (all rows where this condition applies for those rows. For Example, I would want to EXCLUDE all of the highlighted user_integration_ids in the attached screenshot. Thanks so much tips or hints.
1 Reply
I figured it out.
SELECT DISTINCT
USER_INTEGRATION_ID
FROM
(
SELECT
USER_INTEGRATION_ID
,PICK
,MIN(PICK) OVER(PARTITION BY USER_INTEGRATION_ID) AS MINPICK
,MAX(PICK) OVER(PARTITION BY USER_INTEGRATION_ID) AS MAXPPICK
FROM
STUPID)
AS q
WHERE
MINPICK<=2 AND
MAXPPICK<=2...Seems to work fine.
