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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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, pdate FROM logging
  ORDER BY pdate asc
--GROUP BY module

Result without "GROUP BY" (first 12 lines):


Result incl. "GROUP BY":


As one can see the "GROUP BY" produces a completely different order.



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
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 Noseshine 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 Noseshine 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
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, 
      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 Noseshine 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 Noseshine 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
Works - marked as solution. Too bad this isn't nearly as simple as the Unix text pipe solution... – Mörre Noseshine Aug 9 '11 at 12:21

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

    logging L
    SELECT module, MIN(PDATE) AS MinPDate FROM logging GROUP BY module
    ) C ON L.module = C.module AND C.MinPDate = 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 Noseshine Aug 9 '11 at 11:17

Your Answer


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.