Max Date in Oracle SQL

Hi Guys,

I'm fairly new to Oracle sql and trying to figure a way to find the max date on a field that is greater than 1/1/2015.
I'm trying to use something like this in my where clause.
AND   ben.start_date=(select max(ben.start_date) >= to_date( '01-jan-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss' ) from PROD.benefit)

Any suggestions on how I can resolve this?

Thanks
metalteckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Oracle dates have a time portion built in.  If the apps populate it, you might not get the rows you want back using MAX that way.


Put your filter on the where clause:
select max(ben.start_date) from PROD.benefit where start_date >= to_date( '01-jan-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )

BUT, you shouldn't have to to the select this way.  You should be able to use the ROW_NUMBER window function to return the max and only hit the table once.

If you could provide some sample data and expected results, we can post an entire query.
0
slightwv (䄆 Netminder) Commented:
Here is a test case that shows what you are trying and what I think it should be.  I used dense_rank instead of row_number to reproduce the results I think you want.

If this isn't what you are after, please add to the test case and provide expected results:
/*
drop table tab1 purge;
create table tab1(col1 char(1), start_date date);

insert into tab1 values('a',to_date('12/31/2014','MM/DD/YYYY'));
insert into tab1 values('a',to_date('01/01/2015','MM/DD/YYYY'));
insert into tab1 values('a',to_date('01/02/2015','MM/DD/YYYY'));

insert into tab1 values('b',to_date('12/31/2014','MM/DD/YYYY'));

insert into tab1 values('c',to_date('01/02/2015','MM/DD/YYYY'));
insert into tab1 values('c',to_date('01/02/2015','MM/DD/YYYY'));

insert into tab1 values('d',to_date('01/02/2015','MM/DD/YYYY'));
commit;

*/

select col1, start_date
from tab1
where
	start_date=(select max(start_date) from tab1 where start_date >= to_date( '01-jan-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss' ))
/

select col1, start_date from
(
	select col1, start_date, dense_rank() over(order by start_date desc) rn
	from tab1
)
where rn=1
/

Open in new window


My results:
C START_DATE
- -------------------
a 01/02/2015 00:00:00
d 01/02/2015 00:00:00
c 01/02/2015 00:00:00
c 01/02/2015 00:00:00

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
using MAX() OVER() might also work

e.g.
select * from (
    select *
    ,  MAX(ben.start_date)  OVER(PARTITION BY ben.employee)  as max_sdate
    from ...
   )
where max_sdate > to_date( '01-jan-2015','dd-mon-yyyy')

BUT if your ultimate aim is to get just one row from the query (per employee) and that row holds "the maximum start date", then I would suggest you use ROW_NUMBER()

e.g.
select * from (
    select *
    ,  ROW_NUMBER()  OVER(PARTITION BY ben.employee ORDER BY ben.start_date DESC)  as rn
    from ...
   )
where rn = 1
0
awking00Commented:
The suggestions posted above most assuredly put you on the right track but, if you would post the entire query that you attempted, I'm sure the experts would provide you with precisely what you need.
0
awking00Commented:
Only because I don't type so well, I would use >= trunc(sysdate,'yy') :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.