CODEWITHSUNDEEP
mysqlunion
user1772498
user1772498

Reputation: 47

UNION of (2) SELECT with WHERE, GROUP BY and ORDER BY from (1) table

I want to combine two queries with different WHERE clauses and overlapping results. How can I use UNION to combine the result to be distinct by case_id?

First query

SELECT
O.case_id,
O.otc_eff_date,
DATE_FORMAT(STR_TO_DATE(O.otc_eff_date, '%Y%j'),'%m/%d/%Y') otc_ef_date,
O.otc_type,
O.case_rsf,
O.otc_rsn,
O.otc_rlse_to,
O.seg_ent_date,
O.Seg_chg_date,
O.otc_case_status,
O.otc_opn_seq_cnt,
O.filler
FROM NU.RawCaseOTC O
WHERE O.otc_eff_date BETWEEN 2011182 AND 2012182
GROUP BY O.case_id
ORDER BY otc_ef_date

Second query

SELECT 
MAX(otc_eff_date) MAX_OTC_EFF_DATE, 
case_id,
otc_eff_date,
otc_type,
case_rsf,
otc_rsn,
otc_rlse_to,
seg_ent_date,
Seg_chg_date,
otc_case_status,
otc_opn_seq_cnt,
filler 
FROM NU.RawCaseOTC 
WHERE (otc_case_status != 'C') AND (otc_eff_date <='2011182')
GROUP BY case_id
ORDER BY otc_eff_date

Upvotes: 2

Views: 4285

Answers (2)

Spudley
Spudley

Reputation: 168655

Be careful! Yes, you can do things like wrap the two queries in a nested SELECT with a GROUP BY on the outer query... but it will absolutely kill your performance, because the DB will need to save the whole of the output of the inner queries to a temp table and requery them. If you've got even a moderate number of records being queried, it's just not going to be pretty.

You'd be much better off trying to combine the two queries into a single query in the first place.

When you're looking at queries like this it can get complex to do it, but it can be done, and the performance ought to be significantly better.

I don't have time to come up with the perfect query for you right now, but there have been a few other questions here and elsewhere about how to do this sort of thing. Maybe one of these will help you?

Upvotes: 0

w770115
w770115

Reputation: 187

If you UNION this 2 selects and then make another select over it, you can make case_id distinct if you put each other columns in MAX or MIN or SUM or COUNT or AVG ...

If you want first row to be distinct, second row must be in aggregate function.

Upvotes: 1

Related Questions