Thanks for the question.

Asked: September 02, 2008 - 2:43 pm UTC

Last updated: September 04, 2008 - 8:43 am UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Here are the table create and insert scripts:

CREATE TABLE TEST
(
HTENANT NUMBER NOT NULL,
HMY NUMBER NOT NULL,
SACTIONTYPE2A CHAR(1 BYTE),
DTEFFECTIVE2B DATE
);

Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 137500, '2', TO_DATE('02/01/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 197822, '2', TO_DATE('07/01/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 69815, '2', TO_DATE('07/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 368909, '2', TO_DATE('07/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 45, '3', TO_DATE('09/07/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 362238, '3', TO_DATE('09/07/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(35, 37030, 'D', TO_DATE('03/11/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 51285, '2', TO_DATE('12/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 87, '3', TO_DATE('01/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 142590, '3', TO_DATE('01/01/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 175343, '3', TO_DATE('05/01/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 60878, '6', TO_DATE('11/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 308279, '6', TO_DATE('11/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST
(HTENANT, HMY, SACTIONTYPE2A, DTEFFECTIVE2B)
Values
(77, 49432, 'D', TO_DATE('10/03/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

I'm looking for a SQL script that will give me the following output:

HTENANT SACTIONTYPE2A DATE SACTIONTYPE2A DATE SACTIONTYPE2A DATE SACTIONTYPE2A DATE
----------------------------------------------------------------------------------------------------------------------
35 2 7/1/2007 3 9/7/2006 D 3/11/2005 6 NULL
77 2 12/1/2005 3 5/1/2006 D 10/3/2005 6 11/30/2006

i.e. for each htenant i need the most recent date per sactiontype2a.

Thanks Tom for all your help in advance.

and Tom said...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122801500346829407

demonstrates various approaches do this

row_number() over (partition by htenant order by sactiontype2a desc)

would be useful, group by htenant with a keep (dense_rank order by sactiontype2a desc) would be as well.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 02, 2008 - 7:43 pm UTC

Tom:

i ran into a situation once where the group by query returned an error.

I found that two records had same "created_date" timestamp and it retreived both which resutled in an error.

How do you usually get the last record in these situations.
would you use sequence instead of cerated date? but you mentioned that sequences may not be incremental order if you have several instances.

September 03, 2008 - 10:31 am UTC

hah, that is no error - that is a problem in your data isn't it.

you tell me - how would you get the "last record", you - and you alone - need to define unambiguously what YOUR concept of "last record" is.

row_number will get ONE of them - but a random one of them...

Quibble

Chuck, September 03, 2008 - 11:02 am UTC

Not a problem with the data, a problem with the question being asked of the data ;)

A reader, September 03, 2008 - 11:09 am UTC

Tom,

I get the result, but it is in multiple rows, the output i'm looking for should be in a single line for each htenant.

HTENANT SACTIONTYPE2A DATE SACTIONTYPE2A DATE SACTIONTYPE2A DATE SACTIONTYPE2A DATE
----------------------------------------------------------------------------------------------------------------------
35 2 7/1/2007 3 9/7/2006 D 3/11/2005 6 NULL
77 2 12/1/2005 3 5/1/2006 D 10/3/2005 6 11/30/2006

Thanks Tom.

September 03, 2008 - 12:17 pm UTC

well, that looks like a single line for each htenant to me...

I see a line for 35 and another for 77

what are you seeing?

A reader, September 03, 2008 - 1:03 pm UTC

Tom,
Well that's the desired output, but what I'm getting now is like this:

HTENANT SACTIONTYPE2A DATE
----------------------------------------------------------------------------------------------------------------------
35 2 7/1/2007
35 3 9/7/2006
35 D 3/11/2005
35 6 NULL
77 2 12/1/2005
77 3 5/1/2006
77 D 10/3/2005
77 6 11/30/2006

Thanks Tom.

September 03, 2008 - 4:12 pm UTC

and sigh, you haven't given us the query you are using.

so we have NO CLUE WHATSOEVER what you are doing.

did you read the link above, it demonstrates two methods - one with row_number and one with aggregates

which did you use and what does your query look like.

I gave you the row_number() function to use - did you?

A reader, September 03, 2008 - 5:12 pm UTC

Here is the query:

SELECT *
FROM (SELECT htenant, hmy, sactiontype2a, dteffective2b,
ROW_NUMBER () OVER (PARTITION BY htenant, sactiontype2a ORDER BY sactiontype2a DESC)
latest
FROM TEST)
WHERE latest = 1;

September 03, 2008 - 5:34 pm UTC

so, why did you partition by sactiontype2a

I wrote above

... row_number() over (partition by htenant order by sactiontype2a desc) ...

you want to break the data up by htentant (partition by) and then sort that partition from big to small by the date and assign a row_number.

A reader, September 03, 2008 - 8:38 pm UTC

Hi Tom,

I'm partitioning by htenant and sactiontype2a, because i need the latest entry for each sactiontype2a per htenant.

For ex: htenant 35 has (3) sactiontype2a i.e. '2','3' and 'D'. I need the latest entry for each of this sactiontype2a i.e. '2','3' and 'D' for htenant '35' in a single row.

HTENANT SACTIONTYPE2A DATE SACTIONTYPE2A DATE SACTIONTYPE2A DATE SACTIONTYPE2A DATE
----------------------------------------------------------------------------------------------------------------------
35 2 7/1/2007 3 9/7/2006 D 3/11/2005 6 NULL

Hope I'm clear Tom.

Thanks for your patience and help.

September 04, 2008 - 8:43 am UTC

are '2', '3', and 'D' the only possible values.

if so, search this site for "pivot", you want to pivot the result set.

If not, we have a problem - a sql query has a fixed number of columns. If the number of types changes from run to run, if the values change from run to run you would need to

a) query the distinct types up
b) build a query that pivots that set of types based on a

most recent record

A reader, September 24, 2008 - 11:10 pm UTC

Tom:

Is this the best way to write this. I want to find the books on a given server
and then find the most recent vendor and most recent review stage.
In rev_stages table I may get multiple records with same created date. I want to get
the record with highest created date and stage no.

book_status
-----------
bkno number(10) PK,
server_id varchar2(3) PK,
created_date date

Vendor_upld
----------
upload_id number(10) PK,
vendor_id number(10),
bkno number(10),
created_date date

REV_STAGES
-----------
bkno number(10),
stage_no number(1),
created_date date

select x.bkno,y.vendor,z.stage_no,x.created from
(SELECT bkno,created_date from book_Status A where a.server_id='A' ) X,
(SELECT c.vendor_id,c.bkno from vendor_upld c WHERE created_date=(select max(created_date) from vendor_upld where bkno=c.bkno) ) Y,
(SELECT bkno,stage_no from REV_STAGES d where created_date=(select max(created_date) from REV_STAGES where bkno=d.bkno) ) Z
where X.bkno=Y.bkno(+) and X.bkno=Z.bkno;

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Quibble

most recent record

More to Explore

Analytics

SQL query to get the most recent entry from a group...

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Quibble

most recent record

More to Explore

Analytics