Home » SQL & PL/SQL » SQL & PL/SQL » I wan to find the biggest age number
I wan to find the biggest age number [message #445544] Tue, 02 March 2010 07:24 Go to next message
rtumatt
Messages: 4
Registered: March 2010
Junior Member
Hi i want a simple query to find out for example the highest age in the table. Sorry my terminology is not very good i am new to SQL and oracle. Thankyou and i appreciate any replies
Re: I wan to find the biggest age number [message #445549 is a reply to message #445544] Tue, 02 March 2010 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
max
Re: I wan to find the biggest age number [message #445550 is a reply to message #445544] Tue, 02 March 2010 07:44 Go to previous messageGo to next message
rtumatt
Messages: 4
Registered: March 2010
Junior Member
Thankyou for the reply that was really useful. As an improvement to my database is there a way to return todays date and then a date in my database and compair the two and return say an employee that has worked there the longest? This might seem im doing none of the leg work but im really stuck at the moment and i am unable to find any proper examples of that im looking for Confused
Re: I wan to find the biggest age number [message #445552 is a reply to message #445550] Tue, 02 March 2010 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is surely a way to do it but as we don't know what you have you can't say how you can do it.

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: I wan to find the biggest age number [message #445554 is a reply to message #445550] Tue, 02 March 2010 07:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SYSDATE returns the current date and time.

If you do:
select sysdate-<date_column>
from  emp


then it will return you the difference between the date colunm and the current date, in frational days.
Re: I wan to find the biggest age number [message #445561 is a reply to message #445544] Tue, 02 March 2010 08:20 Go to previous messageGo to next message
rtumatt
Messages: 4
Registered: March 2010
Junior Member
Table:
create table o_employees (
eno number(4) not null primary key,
person person_type,
hdate date
);
Person type:
create type person_type as object (
name varchar2(30),
address address_type,
phones phones_varray_type);

Example insert:
insert into o_employees values(1000,person_type('Jones', address_type('123 Main St','Wichita','KS',67226),
phones_varray_type('316-555-1212',null,null)),
'12-DEC-95');


hope this helps.

I am getting this error:

SQL> select sysdate-<hdate>
2 from o_employees;
select sysdate-<hdate>
*
ERROR at line 1:
ORA-00936: missing expression


Thankyou again for the replies i really do appreciate this.
Re: I wan to find the biggest age number [message #445563 is a reply to message #445544] Tue, 02 March 2010 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
The <> are just there to highlight the fact that you need to replace that part of the example with the correct column(s).
Column names in sql queries are never actually wrapped in <>
so:
select sysdate- hdate
from o_employees;

Re: I wan to find the biggest age number [message #445564 is a reply to message #445561] Tue, 02 March 2010 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
<something> means you have to put something there "<>" is not part of the SQL syntax.

'12-DEC-95' is NOT a date it is a string, always use TO_DATE with format model.

I advice you to read Database SQL Reference

Regards
Michel

[Updated on: Tue, 02 March 2010 08:29]

Report message to a moderator

Re: I wan to find the biggest age number [message #445568 is a reply to message #445544] Tue, 02 March 2010 08:37 Go to previous messageGo to next message
rtumatt
Messages: 4
Registered: March 2010
Junior Member
The answer to my question is something like:

select max(hdate) from o_employees;

however retrieving the name from the person type is prooving hard.

[Updated on: Tue, 02 March 2010 08:41]

Report message to a moderator

Re: I wan to find the biggest age number [message #445634 is a reply to message #445568] Wed, 03 March 2010 01:22 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rtumatt wrote on Tue, 02 March 2010 15:37
The answer to my question is something like:

select max(hdate) from o_employees;

Actually, if you want the person that's been working there for the longest period, you want to use min, not max.
Previous Topic: query help
Next Topic: Private Procedure
Goto Forum:
  


Current Time: Wed Dec 07 23:55:53 CST 2016

Total time taken to generate the page: 0.06113 seconds