Annie Jeba Annie Jeba - 2 months ago 11
SQL Question

Data sorting and grouping in Oracle

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.

ID Category
45678 A
234567 B
-4578 A
-45798 A
-7890 C
-8904 C
-7654 C

Expected O/P is

ID Category
45678 A
234567 B
-8904 C


"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:

  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 KEEP LAST).