I have a SQL query that fetches one row (LIMIT 1) for a specific client code from the TABLEA based on several conditions. Here’s the query:
SELECT
`TABLEA`.`client_code`
FROM
`TABLEA`
WHERE
(
`TABLEA`.`accounting_date` = 202408
AND (
`TABLEA`.`FLAG1` = ''
OR `TABLEA`.`FLAG1` IS NULL
)
AND `TABLEA`.`client_code` ='A12'
AND (
`TABLEA`.`FLAG2` = ''
OR `TABLEA`.`FLAG2` IS NULL
)
AND (
`TABLEA`.`FLA3` = ''
OR `TABLEA`.`FLA3` IS NULL
)
AND (
`TABLEA`.`payment_closing_flag` = ''
OR `TABLEA`.`payment_closing_flag` IS NOT NULL
)
AND NOT (
`TABLEA`.`FLAG4` = 1
AND `TABLEA`.`FLAG4` IS NOT NULL
AND `TABLEA`.`record_status` = 'D'
AND `TABLEA`.`record_status` IS NOT NULL
AND `TABLEA`.`ABC_CODE` IN (0, 370, 380, 390, 400, 410, 420, 900)
AND `TABLEA`.`ABC_CODE` IS NOT NULL
)
) LIMIT 1;
The query works for a single client_code, but I want to execute it for multiple client_code values (e.g., ‘A12’, ‘A13’, ‘A14’, etc.) and retrieve the LIMIT 1 result for each of those client codes.
Here are my constraints:
I cannot use GROUP BY or DISTINCT due to the large number of rows (millions per client), and I only need to check the existence of one record for each client code.
Performance is critical, and using DISTINCT or GROUP BY is too slow for this dataset.
Is there a way to iterate over multiple client codes and execute this LIMIT 1 query for each client code in a single query or within a single execution block?
You need to sign in to view this answers
Leave feedback about this