-
max() inside a case statement?
Here is a sample of what my table looks like (I actually have a fairly complex query to compile this "table").
cust code auto amount
---- ---- ---- ------
10123 D05 Y 10
10123 FMR Y 7
10123 D40 N 3
10123 VMM N 2
99951 RN5 Y 12
99951 AL7 Y 7
99951 TTE N 5
99951 DEL N 2
Each customer has multiple records. For each customer, I need to retrieve ALL records where auto = 'Y'. Then in addition to those records, for each customer I want to retrieve the auto = 'N' record with the greatest amount.
I'm really stumped on how to do this...my only guess is maybe use max inside a case statement?? Any suggestions would be greatly appreciated!
-
You might want to use a union:
select * from table where auto='Y'
union all
select cust,code,auto,amount from table where auto='N'
having amount= (select max(amount) from table as dummy where dummy.cust=table.cust and auto='N')
The union all is better than union on a performance level, see e.g. http://blog.sqlauthority.com/2009/03...ce-comparison/
-
I thought about a union but the problem with that is I've got a big ugly sub-query to get the records above, so if I use a union than I have to run that twice. I was hoping there would be a more efficient way to do it.
-
You could store the result of your "ugly" subquery in a temp table.
You could also store it in a View, although that would only help for the readability of the query.
You could also modify your original ugly sub-query to also incorporate this union, making it even uglier.
I can't really see a way without using a union, or running the sub-query twice
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|