Select ID'S where each ID having more than 3 rows with a column name priority_seq (0, 1, 2, ..3). There are ID's which is not having 0 priority_seq at all in the table.I need to select those those Id's which do not have 0 in priority_seq at all.
SELECT ID
FROM table_name
GROUP BY ID
HAVING
/*ID does not have any priority_seq of 0*/
MAX(CASE WHEN priority_seq = 0 THEN 1 ELSE 0 END) = 0
/*if needed, check if ID also has at least 3 rows with priority_seq not null)
AND COUNT(priority_seq) >= 3 /* naturally delete this line if not needed*/
its not giving any result....??? but there are master_customer_id's where priority_seq =0 is not there but they are having 1, 2, 3 or more...
Select ID'S where each ID having more than 3 rows with a column name priority_seq (0, 1, 2, ..3). There are ID's which is not having 0 priority_seq at all in the table.I need to select those those Id's which do not have 0 in priority_seq at all.
But above query displaying id's which is having zero. i dont need id which is having zero..
MAybe this:
SELECT DISTINCT ID
FROM table_name AS T1
WHERE NOT EXISTS
(
SELECT *
FROM table_name AS T2
WHERE T2.ID = T1.ID
AND T2.priority_seq = 0
)
one more condition: address_status_change: 'work' and 'home'
some thing like this:
SELECT DISTINCT CUS_ADDRESS_ID
FROM CUS_ADDRESS_DETAIL AS T1
WHERE NOT EXISTS
(
SELECT *
FROM CUS_ADDRESS_DETAIL AS T2
WHERE T2.CUS_ADDRESS_ID = T1.CUS_ADDRESS_ID
AND T2.priority_seq = 0 AND ADDRESS_STATUS_CHANGE IN('WORK',HOME')
)
IS THIS RIGHT??? WHEN I AM EXECUTINT ITS GIVING ALL ID'S
The query I wrote will not return any ID that has a priority_seq of 0. You must have made changes to the query. I don't know what changes you made, and without that info I can't help you further.
yes..i made changes. i just need to add one more condition where address_type_code in ('home', 'work') me id's which is not having priority_seq is 0. (note: address_type_code is a column)
yes..i made changes. i just need to add one more condition where address_type_code in ('home', 'work') to id's which is not having priority_seq is 0. (note: address_type_code is a column)
SELECT CUS_ADDRESS_ID
FROM CUS_ADDRESS_DETAIL
GROUP BY CUS_ADDRESS_ID
HAVING
/* make sure NO row matches these conditions */
MAX(CASE WHEN address_type_code IN ('home', 'work') AND priority_seq = 0
THEN 1 ELSE 0 END) = 0
