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;
I use AQ for
- transactions between databases
- implementing business rules that would otherwise need to be implemented using triggers ( ie actions must be taken on the same table that initiates the action)
By using a queue you can do these things:
- a transaction can take place in the order you want it to, just not instantaneously. eg insert in one database, copy same record to another database
- the second transaction is now independent of the first but you still have consistency. eg only if the first insert succeeds does the second AQ transaction take place and if the second transaction takes place on another database it only happens if the second database is ready to receive
Here is more information on how I use AQ between databases. I am not an expert and got most of the code from the Internet. Oracle documentation is lengthy but did not really help me.
First create the queue:
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'QT_NEW_CASE'
,QUEUE_PAYLOAD_TYPE => 'FILE_ACTION'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => '
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
,SECURE => FALSE
);
End;
On the origin database:
CREATE OR REPLACE TYPE FILE_ACTION AS OBJECT
( ACTION VARCHAR2(20),
CASE_ID NUMBER(10),
OTHER VARCHAR2(20)
);
The Action is admittedly crude but versatile. The requirements were to funnel changes from multiple tables to another database where further processing was required without touching the application code. A typical call is to a package from a trigger, other procedure or job.
queue_util.add_file ('CLOSE', v_case_id,:NEW.ID);
Inside the package
PROCEDURE add_file_to_queue (action_in IN VARCHAR2,
d_case_id_in IN NUMBER,
d_other_in IN VARCHAR2:= NULL)
IS
/******************************************************************************
PURPOSE: when there is a change to a file (create, closed or reopen) add the change to the queue of changes
******************************************************************************/
queue_options SYS.DBMS_AQ.enqueue_options_t;
message_properties SYS.DBMS_AQ.message_properties_t;
message_id RAW (16);
my_message file_action;
err_text VARCHAR2 (2000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
my_message := file_action (action_in, d_case_id_in, d_other_in);
DBMS_AQ.enqueue (queue_name => 'NEW_CASE_QUEUE',
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
IF g_debugging
THEN
;
--insert debugging info if g_debugging is true
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
err_text := SQLERRM;
--logging error to another table
WHEN OTHERS
THEN
err_text := SQLERRM;
--more logging
END add_file_to_queue;
--and popping messages off the queue
PROCEDURE send_from queue (case_id_in IN NUMBER := NULL)
IS
/******************************************************************************
PURPOSE:get the list of file changes and send them out
*****************************************************************************/
queue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_id file_action;
v_file VARCHAR2 (20);
v_case_id NUMBER (10);
v_filename VARCHAR2 (500);
v_action VARCHAR2 (20);
v_other VARCHAR2 (20);
v_err_id INTEGER;
bad_data_ex EXCEPTION;
v_err_text VARCHAR2 (50);
TYPE cases_cur IS REF CURSOR;
new_cases cases_cur;
BEGIN
IF case_id_in IS NULL
THEN
OPEN new_cases FOR
SELECT qt.msgid
FROM cqt_new_case qt
ORDER BY qt.enq_time;
ELSE
OPEN new_cases FOR
SELECT qt.msgid
FROM qt_new_case qt
WHERE qt.user_data.case_id = case_id_in
ORDER BY qt.enq_time;
END IF;
--should have added a check here to make sure
--the other database is up and running
LOOP
BEGIN
FETCH new_cases INTO message_id;
--reinitialize values to null
v_case_id := NULL;
v_filename := NULL;
v_file := NULL;
v_action := NULL;
v_other := NULL;
--to try and clear all locks
COMMIT;
EXIT WHEN new_cases%NOTFOUND;
IF case_id_in IS NOT NULL
THEN
queue_options.deq_condition :=
'tab.user_data.case_id = ' || case_id_in;
END IF;
DBMS_AQ.dequeue (queue_name => 'NEW_CASE_QUEUE',
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
v_case_id := my_message.case_id;
v_action := my_message.action;
v_other := my_message.other;
IF v_case_id IS NOT NULL AND v_case_id > 0
THEN
IF g_debugging
THEN
;
--insert your debugging information
END IF;
--continues on with lengthy data transforms
--for actions like NEW, CLOSE, REOPEN
END SEND_FROM_QUEUE;
I would certainly write this differently today but it works.... The key problem with AQ for me is that I have never gotten queue to queue messaging going between different databases. This was described as a primary value for it. Yet I still like having the certainty that the initial transaction can complete without regard for the state of the destination database. If the message fails to be inserted when into the destination database an error is logged and an email is sent notifying the developer.
Best Answer
Yes, if I understand correctly what you are asking.
The difference between query 1 and 2 and is that query 1 returns more information than 2. It includes columns
col3andcol4in the output.While query-2 returns all the distinct values of col1 and col2, query-1 returns all the distinct values of col1 and col2 plus the values of col3 and col4 from the first row (ordered by col3) of each group (ie. each col1 + col2 combination).
Yes, if you don't need the rest of the columns, but only col1 and col2, there is no difference.