Some one mistakenly inputted negative values to unique key column
long back and now i have to group the data selecting max of ID as per category to extract report. ID column now have both positive and negative values.
Max(ID) function is not working correctly with negative values.
"So ID with largest negative values will have latest data before 2010 and id with positive values are created after 2010"
That means in case there are positive IDs for a category you want the maximum (e.g. 45678 for category A) and otherwise the minimum (e.g. -8904 for category C). You can use Oracle's
KEEP FIRST/LAST for this:
select category, max(id) keep (dense_rank last order by sign(id), abs(id)) from mytable group by category order by category;
This sorts your IDs by sign (negative before positive ones, so if there are positive ones you'd prefer these) and then by absolute amount (so you get the highest negative or positive as the last row, which is the one you pick with