OraFAQ Forum: SQL & PL/SQL » SQL query help

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL query help [message #639398]

Mon, 06 July 2015 14:00

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Hello all,

I want to write sql query first sort by create date then upon sort fetch the first record

Below is my sql query, but it is giving error ORA-00907: missing right parenthesis

SELECT *
FROM ord
WHERE id IN
  (SELECT id
  FROM ord
  WHERE id LIKE '09%'
  ORDER BY created_dttm DESC
  )
AND rownum <=1;

could you let me know what is wrong here

Report message to a moderator

Re: SQL query help [message #639401 is a reply to message #639398]

Mon, 06 July 2015 14:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

remove the ORDER BY clause.

Report message to a moderator

Re: SQL query help [message #639402 is a reply to message #639398]

Mon, 06 July 2015 14:30

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

I think you might try another approach, using max(created_ddtm)

Report message to a moderator

Re: SQL query help [message #639404 is a reply to message #639398]

Mon, 06 July 2015 14:39

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

IN is an operator on a set: "IN (set of values)". A set has no order. Your approach is wrong.
Investigate John hint or a ranking function.
The solutions depend on what you are actually trying to do.

Report message to a moderator

Re: SQL query help [message #639408 is a reply to message #639404]

Mon, 06 July 2015 20:25

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

i dont find better way of achieving this.

Report message to a moderator

Re: SQL query help [message #639409 is a reply to message #639408]

Mon, 06 July 2015 20:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

WITH high_val 
     AS (SELECT id 
         FROM   ord 
         WHERE  id LIKE '09%' 
         ORDER  BY created_dttm DESC) 
SELECT * 
FROM   ord 
WHERE  id IN (SELECT id 
              FROM   high_val) 
       AND ROWNUM <= 1

Report message to a moderator

Re: SQL query help [message #639410 is a reply to message #639409]

Mon, 06 July 2015 22:13

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

i tried , it is not working as expected. it is always giving 4th record from result not the 1st record.

Report message to a moderator

Re: SQL query help [message #639414 is a reply to message #639410]

Mon, 06 July 2015 23:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post Test Case

WITH high_val 
     AS (SELECT id 
         FROM   ord 
         WHERE  id LIKE '09%') 
SELECT Max(id) 
FROM   high_val;

Report message to a moderator

Re: SQL query help [message #639419 is a reply to message #639410]

Tue, 07 July 2015 00:05

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

gorants wrote on Tue, 07 July 2015 05:13

i tried , it is not working as expected. it is always giving 4th record from result not the 1st record.

As we have not the specification of what you are trying to do, we can only make blind shot until we'll find a query which we'll give the expected result which may not the correct one.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Report message to a moderator

Previous Topic:	To get start and end date of month
Next Topic:	PL/SQL Cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 07:40:29 CDT 2024