max() inside a case statement?

[jlp44]

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!

[Gil-Galad]

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/

[jlp44]

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.

[Gil-Galad]

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