Home » SQL & PL/SQL » SQL & PL/SQL » QUERY PROBLEM
QUERY PROBLEM [message #569187] Mon, 22 October 2012 08:47 Go to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
Dear all


suppose this query
SELECT MAX(GP.ISSUE_DATE),GE.INSURANCE_AMOUNT 
FROM GI_EMPLOYEE_POLICIES GE , GI_POLICIES GP
WHERE GE.POLICY_ID = GP.GI_POLICY_ID
AND GE.EMP_ID =6
GROUP BY GE.INSURANCE_AMOUNT 


this results


MAX(GP.ISSUE_DATE) INSURANCE_AMOUNT
10/10/2012 100
02/01/2012 100000
12/10/2012 500


i want only to retrieve one row with the most recent date
so that i want the result be like that



MAX(GP.ISSUE_DATE) INSURANCE_AMOUNT
12/10/2012 500


Thanks
Re: QUERY PROBLEM [message #569189 is a reply to message #569187] Mon, 22 October 2012 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: QUERY PROBLEM [message #569190 is a reply to message #569187] Mon, 22 October 2012 09:01 Go to previous messageGo to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
Read it before
is there anything not applicable or not clear in my post

[Updated on: Mon, 22 October 2012 09:01]

Report message to a moderator

Re: QUERY PROBLEM [message #569191 is a reply to message #569190] Mon, 22 October 2012 09:03 Go to previous messageGo to next message
Roachcoach
Messages: 1219
Registered: May 2010
Location: UK
Senior Member
14:57:57 SQL> SELECT MAX(GP.ISSUE_DATE),GE.INSURANCE_AMOUNT
15:02:22   2  FROM GI_EMPLOYEE_POLICIES GE , GI_POLICIES GP
15:02:22   3  WHERE GE.POLICY_ID = GP.GI_POLICY_ID
15:02:22   4  AND GE.EMP_ID =6
15:02:22   5  GROUP BY GE.INSURANCE_AMOUNT
15:02:23   6  /
FROM GI_EMPLOYEE_POLICIES GE , GI_POLICIES GP
                               *
ERROR at line 2:
ORA-00942: table or view does not exist

Re: QUERY PROBLEM [message #569192 is a reply to message #569190] Mon, 22 October 2012 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
emadnabil wrote on Mon, 22 October 2012 16:01
Read it before
is there anything not applicable or not clear in my post


With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Is there anything not clear in this?

Regards
Michel
Re: QUERY PROBLEM [message #569193 is a reply to message #569187] Mon, 22 October 2012 09:21 Go to previous messageGo to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
No thanks
I will solve it myself
Re: QUERY PROBLEM [message #569194 is a reply to message #569193] Mon, 22 October 2012 09:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
A hint:

SQL> select  max(hiredate),
  2          sal
  3    from  emp
  4    group by sal
  5    order by max(hiredate)
  6  /

MAX(HIRED        SAL
--------- ----------
17-DEC-80        800
20-FEB-81       1600
02-APR-81       2975
01-MAY-81       2850
09-JUN-81       2450
08-SEP-81       1500
28-SEP-81       1250
17-NOV-81       5000
03-DEC-81        950
23-JAN-82       1300
19-APR-87       3000

MAX(HIRED        SAL
--------- ----------
23-MAY-87       1100

12 rows selected.

SQL> select  max(max(hiredate)),
  2          max(sal) keep(dense_rank last order by max(hiredate))
  3    from  emp
  4    group by sal
  5  /

MAX(MAX(H MAX(SAL)KEEP(DENSE_RANKLASTORDERBYMAX(HIREDATE))
--------- ------------------------------------------------
23-MAY-87                                             1100

SQL> 


SY.
Re: QUERY PROBLEM [message #569199 is a reply to message #569187] Mon, 22 October 2012 10:39 Go to previous messageGo to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
Thanks Solomon Yakobson

and here is another solution

select  GE.INSURANCE_AMOUNT
from  GI_EMPLOYEE_POLICIES GE 
where GE.EMP_ID =6
AND  GE.POLICY_ID  IN (SELECT  GP.GI_POLICY_ID FROM GI_POLICIES GP
WHERE GP.ISSUE_DATE =(SELECT MAX(ISSUE_DATE) FROM GI_POLICIES GP , GI_EMPLOYEE_POLICIES GE
WHERE GE.POLICY_ID = GP.GI_POLICY_ID
AND  GE.EMP_ID =6
  )
  AND GP.POLICY_NO = 1)


Re: QUERY PROBLEM [message #569203 is a reply to message #569187] Mon, 22 October 2012 13:00 Go to previous message
joy_division
Messages: 4528
Registered: February 2005
Location: East Coast USA
Senior Member
emadnabil wrote on Mon, 22 October 2012 09:47
i want only to retrieve one row with the most recent date
so that i want the result be like that


You had the same exact issue over 2 years ago. Why the problem still?

http://www.orafaq.com/forum/m/451294/66800/#msg_451294
Previous Topic: Table Instance Chart Question
Next Topic: multiple report parameter values
Goto Forum:
  


Current Time: Wed Oct 22 03:36:52 CDT 2014

Total time taken to generate the page: 0.23638 seconds