Sysdate in Oracle SQL Plus - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sysdate in Oracle SQL Plus

Author

Topic

antgaudi
Starting Member

4 Posts

Posted - 2009-04-19 : 19:30:21

I have this code:
select distinct pil_pilotname, dep_dep_date
from departures, pilots
where dep_dep_date < sysdate
and dep_pilot_id = pil_pilot_id;

The result gives me:
PIL_PILOTNAME DEP_DEP_D
------------------------------ ---------
Leiss, Ernst L. 19-APR-09
Harris, Kenneth C. 18-APR-09
Long, Stuart 18-APR-09
Long, Stuart 19-APR-09
Long, Stuart 08-APR-09
Harris, Kenneth C. 08-APR-09
Leiss, Ernst L. 08-APR-09
Pasewark, William B. 10-APR-09
Harris, Kenneth C. 02-APR-09

PIL_PILOTNAME DEP_DEP_D
------------------------------ ---------
Pasewark, William B. 17-APR-09
Pasewark, William B. 01-APR-09
Harris, Kenneth C. 19-APR-09
Pasewark, William B. 08-APR-09

I'm trying to retrieve only the most recent departure date for each pilot. What am I missing

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-19 : 20:08:47

This is a Microsoft SQL Server site. Although you may find someone here who can help you, you'd have better luck on a site that specializes in Oracle. Try dbforums.com, it has an Oracle forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-20 : 04:13:07

Try this too

select pilots.pil_pilotname, max(dep_dep_date) as dep_dep_date
from departures inner join pilots
on departures.dep_pilot_id = pilots.pil_pilot_id
where departures.dep_dep_date < sysdate
group by pilots.pil_pilotname;

Madhivanan

Failing to plan is Planning to fail

antgaudi
Starting Member

4 Posts

Posted - 2009-04-20 : 12:56:26

Yours works great.
Do you happen to know the difference though, between your input and the one below:

select pil_pilotname, max(dep_dep_date)
from departures, pilots
where dep_dep_date < sysdate
and dep_pilot_id = pil_pilot_id
group by pil_pilotname
/

I noticed that both give the same results but I don't know which query would be the best (most updated) to use. Does it matter?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-20 : 13:44:51

Run them both in the same batch and compare the execution plans. If they both say 50% for the cost of the batch, then you can pick either one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-21 : 02:08:22

quote:
Originally posted by antgaudi

Yours works great.
Do you happen to know the difference though, between your input and the one below:

select pil_pilotname, max(dep_dep_date)
from departures, pilots
where dep_dep_date < sysdate
and dep_pilot_id = pil_pilot_id
group by pil_pilotname
/

I noticed that both give the same results but I don't know which query would be the best (most updated) to use. Does it matter?

Thanks.

I used ANSI join

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources