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.