Hi ...
I've get stuck with a "little" problem.
I try to provide some testing code for this:
CREATE TABLE a1 (
id NUMBER(8),
val NUMBER(6),
title VARCHAR2(16),
CONSTRAINT test_pk PRIMARY KEY(id)
);
INERT INTO a1 (id, val, title) VALUES (1,12,'c');
INERT INTO a1 (id, val, title) VALUES (2,13,'b');
INERT INTO a1 (id, val, title) VALUES (3,13,'a');
INERT INTO a1 (id, val, title) VALUES (4,13,'a');
INERT INTO a1 (id, val, title) VALUES (5,42,'a');
INERT INTO a1 (id, val, title) VALUES (6,42,'b');
INERT INTO a1 (id, val, title) VALUES (7,42,'b');
Actually the table is much bigger ;) But this should be ok for this question. It already exist a query like:
SELECT
count(*) -- just an example
FROM
a1
GROUP BY
val
;
-- should return 1,3,3 (for the groups val=12, val=13,val=42)
Now it is nessecary to select a title for each group (specified by group by). And there the title which occurs the most in this group should be selected. For this example this are 'c' for the group val=12 .. 'a' for the group val=13 and finally 'b' for the group val=42.
I tried to use some anayltical function, but I#m not able to get this to work - may be because I never used analytical functions before. If I try something I mostly get an error: Keyword FROM not at expected position (ORA-00923). I searched for some tutorial/howto documentations where my problem is handled but without success. So I guess the syntax and the way to understand analytical functions is not as easy as it semms to be ...
title OVER ( ORDER BY count(*) ROWS | RANGE BETWEEN 1 AND 1 ) <-- that would by logical for my brain, but not for oracles ;-)
Can somebody help?
Thanks!