I'm looking for a seemingly very simple SELECT.
I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text).
Actually, I care only about the first two columns, i.e. which module has been called at all (which is the name of a procedure in a PL/SQL package). The date I only need for ordering (ASC) the result, and one could also use the ID column for the same purpose, which is an auto-incremented integer value (Oracle: sequence.nextval).
Since the logging produces waaaaayyyyy too much data, tens to hundreds of PTEXT entries for a single MODULE, I would like to condense the MODULE rows, which seems to call for a simple GROUP BY.
Maybe this illustrates the issue:
I start with the inner query below, which gives me the correct sequence of "module" calls, but does not aggregate them (example 1 above). So my idea was okay, I get the correct result in the correct order - so if I now select just the one column I'm interested in and use "group by" on this ordered(!) data I should get what I want - but adding the "group by" below gives me a COMPLETELY different order. Apparently my knowledge about what "group by" does is inadequate.
SELECT module FROM
(
SELECT module, pdate FROM logging
ORDER BY pdate asc
)
--GROUP BY module
Result without "GROUP BY" (first 12 lines):
set_option_value
set_option_value
set_option_value
set_option_value
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_fun_AppOptionsSingleVal
get_rights_usecase
...
Result incl. "GROUP BY":
get_fun_AppOptionsSingleVal
get_option_value
get_parameter
get_usecase_customer
get_kungesId_FY_cusper
fill_missing_products
update_chart_budget_uc1
get_Berein
set_hierarchy
get_element_num_value
icas_dyn_create_chart_table
get_xmlTemplate
...
As one can see the "GROUP BY" produces a completely different order.
What I WOULD HAVE WANTED:
set_option_value
get_rights_usecase
get_fun_AppOptionsSingleVal
get_rights_usecase
...
Basically, what I want can be achieved very simply by taking the text output of
SELECT module FROM logging
ORDER BY pdate asc
and pipe it through "uniq" on the Unix command line. Now I'm just curious why this isn't as simple in SQL.