Forum Stats

  • 3,769,306 Users
  • 2,252,946 Discussions
  • 7,874,982 Comments

Discussions

Query tuning

User_R4VJD
User_R4VJD Member Posts: 31 Green Ribbon

Selecting max(create_date) based on where condition.

Create date < trunc(sysdate)

Requirements : max(create_date) < trunc(sysdate)

Eg :

Scenario 1:

sysdate - 09-06-21

Max(create_date) - 09-06-21

Should return no.rows

Scenario 2:

sysdate - 09-06-21

Max(create_date) - 08-06-21

Should return 1 row

Select count (*)

From tab1

where create_date = (select max(create_date) from tab1 where code = '100' and loc='Den')

And code = '100' and loc='Den'

And create_date < trunc(sysdate);

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @User_R4VJD

    Whenever you have a tuning question, please post the explain plan output, and give more details about your tables (e.g., which columns are unique, have indexes, can't be NULL, ...). Whenever you have any question, post your full Oracle version (e.g., 12.2.0.1.0).

    Try writing the query like this:

    WITH  got_max_create_date  AS
    (
    	SELECT   create_date
    	FROM	 tab1
    	WHERE	 code = 100
    	AND	 loc  = 'Den'
    	ORDER BY create_date DESC
    	FETCH 	 FIRST 1 ROWS WITH TIES
    )
    SELECT	COUNT (*)		AS cnt
    ,	MAX (create_date)	AS max_date -- if wanted
    FROM	got_max_create_date
    HAVING	MAX (create_date) < TRUNC (SYSDATE)
    ;
    

    Of course, I can't test it without any sample data.