How to Remove Duplicate with precedence of a particular field in mysql select query?-CodePudding

I've a mysql query which returns few records with duplicates. I need to get the unique record when both "Success" and "Error" exist in mysql result set and it should consider only "Success" as precedence. In below example - Job name "Timothy Plan Global CrRd" has two records - Success and Error. I need to get only Success one. I tried with group by clause on job_name but it only picks up Error one in result set. But I want the status with success one as precedence. Can you please help?

Here is the query -

select status as 'Status', job_logid as 'Job Log ID', job_name as 'Job Name', job_type as 'Job Type', file_name as 'File Name', start_time as 'Run Date/Time', run_user as 'Run User' from etp.job_log
where DATE(start_time) = Date(now())-1 and job_type = 'Outbound'
and job_name like '%CrRd%'
order by job_name;

Here is the correct query by David. Thank you.

select max(status) as 'Status', job_logid as 'Job Log ID', job_name as 'Job Name', job_type as 'Job Type', file_name as 'File Name', start_time as 'Run Date/Time', run_user as 'Run User' from etp.job_log
where DATE(start_time) = Date(now())-1 and job_type = 'Outbound'
and job_name like '%CrRd%'
group by job_name

CodePudding user response：

I suggest using select select max(status) as 'Status', that could do the job of getting first the status 'Success'. Then we can use group by job_logid,job_name to preserve the duplicates from the one that doesnt have an error and avoid to select the ones that have an error after o before a Success status.