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*/
1 Like
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
)
1 Like
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
I need to get most recent date out of these two columns..
first look for most recent date in Moddate, if moddate is null ..go to adddate and get most recent date from adddate.
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
1 Like
