I find this just messed up.. but maybe this is the way it is supposed to work, but I never knew about it. I can't find any documentation on this behavior
For example, Say I have a paint shop, and colors get new formulas, but keep the same name, so they have a new version and keep the same name, and each formula get a new ID (primary key)
oracle 11g r2
TABLE colors
Name Null? Type
----------------------------------------- -------- ----------------------------
COLOR VARCHAR2(30)
VERSION NUMBER
ID NUMBER
In the following table, Blue has 3 revisions, so the latest version would be 3 (red 1, and purple 2)
Select * from colors
COLOR VERSION ID
------------------------------ ---------- ----------
blue 1 100
blue 2 102
blue 3 103
red 1 200
purple 1 300
purple 2 301
6 rows selected.
I want to now find the primary key of the highest version of each color. I don't care what the color is, but I want just the 3 PKs of the latest formulas
I understand this behavior, but it won't work for what I want. I want 3 rows, one for each color (without the color itself, JUST the ID):
select max(id) from colors
MAX(ID)
----------
301
1 row selected.
I found this by accident, and I don't understand how this works. This is what I want. I did it by accident and it worked..
But why? I don't see how it can work without a column name before the max group by function
select max(id)
from colors
group by color
MAX(ID)
----------
200
103
301
3 rows selected.
My question is why does this last query work?? Where in the oracle documentation explains how it works?
Is this a supported (or depreciated) feature ?
I have never seen an example with an aggregate function without a field name ( i.e. select color, max(id)..... )