Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
The expected answer: [mod1, mod2, mod1, mod3] doesn't quite respond to [select distinct module], as you can see the mod1 is repeating. So actually you don't want a Select Distinct. Do you have a better prepared set of data? –  Marian Aug 9 '11 at 10:44
    
You are right, "group by" would be a better explanation of what I want. Anyway, this doesn't change the problem. –  Mörre Aug 9 '11 at 11:15
    
So, in order to have a better solution for your problem, please complete the input and then output tables with exactly relevant data. Now it's not clear to me how you want the output data to look. This will help us see if current answers are good or need any other input ;-). –  Marian Aug 9 '11 at 11:39
    
The examnple I gave is sufficient. Instead of "pdate" you could also use "id", an auto-incremented (sequence.nextval) column. Also see my EDIT. –  Mörre Aug 9 '11 at 11:41
    
Are you looking for the last module within each date range or perhaps every module entry from a distinct day/hour/minute? –  Leigh Riffel Aug 9 '11 at 11:58
show 2 more comments

2 Answers

up vote 4 down vote accepted

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate); 

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module, 
             sum(module_step) over (order by pdate rows unbounded preceding) 
                 as batch_number, 
             pdate
      from( select module, 
                   case lag(module) over (order by pdate) when module then 0 
                        else 1 end as module_step, 
                   pdate from logging) )
group by module, batch_number
order by batch_number;
share|improve this answer
    
No, does not work. I get a completely different order than what I should see. I had already tried that :( –  Mörre Aug 9 '11 at 10:20
    
What order do you want to see :) –  Jack Douglas Aug 9 '11 at 10:32
    
As explained in the question. Your idea seems obvious, as I said I tried it myself that way, but it doesn't produce the desired result. This question seems simple but it isn't... –  Mörre Aug 9 '11 at 11:14
    
Ok, you're right, I could have got that from the question - I've edited your question to make it a tiny bit more clear. –  Jack Douglas Aug 9 '11 at 11:39
1  
Works - marked as solution. Too bad this isn't nearly as simple as the Unix text pipe solution... –  Mörre Aug 9 '11 at 12:21
show 3 more comments

You probably want a variation of a previous question How to get the MAX row

SELECT
    L.Module
FROM
    logging L
    JOIN
    (
    SELECT module, MIN(PDATE) AS MinPDate FROM logging GROUP BY module
    ) C ON L.module = C.module AND C.MinPDate = L.PDate
ORDER BY
    L.PDate

EDIT: Have switched to MIN of PDATE as your question uses ORDER BY PDate ASC, which would translate to MIN (MAX would translate to DESC as it returns that latest first)

1: You may want to change that to MIN() depending on how you want it ordered
2: This is code that works on SQL Server, I think it'll work on Oracle...

share|improve this answer
    
Result: Error code 904, SQL state 42000: ORA-00904: "C"."PDATE": invalid identifier - but that's because you took "L" for "C" after the join... :) And no, I get a different sequence compared to when I look at the raw data (and let my brain do the filtering/grouping) –  Mörre Aug 9 '11 at 11:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.