OraFAQ Forum: SQL & PL/SQL » SQL Query Suggestion

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Suggestion (Oracle 12c)

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

SQL Query Suggestion [message #645139]

Fri, 27 November 2015 05:46

msambasiva
Messages: 4
Registered: November 2015

Junior Member

Hi,

Oracle Database 12c Release 12.1.0.1.0 - 64bit

I have a DB Table with the below data,
Doc_Id,Doc_Name,Obsolete
100,Test1,11-27-2015
100,Test1,05-27-2015
100,Test1,07-23-2015
100,Test1,null
200,Test2,10-27-2015
200,Test2,06-27-2015
200,Test2,07-23-2015
200,Test2,03-03-2015

Expected Results:
100,Test1,null
200,Test2,10-27-2015

If the document is not obsoleted(with null i.e 100) then it should return null otherwise it should return latest date

It would be great, if you can suggest a sample query to get the rows.

Thanks in advance,
Samba!

[Updated on: Fri, 27 November 2015 05:48]

Report message to a moderator

Re: SQL Query Suggestion [message #645143 is a reply to message #645139]

Fri, 27 November 2015 07:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: SQL Query Suggestion [message #645144 is a reply to message #645139]

Fri, 27 November 2015 07:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

A hint ROW_NUMBER() OVER(PARTITION BY DOC_ID ORDER BY OBSOLETE DESC NULLS FIRST)

SY.

Report message to a moderator

Re: SQL Query Suggestion [message #645153 is a reply to message #645144]

Fri, 27 November 2015 10:53

msambasiva
Messages: 4
Registered: November 2015

Junior Member

Thanks SY!!
As per your hint, I was able to assign ROW_NUM for each row in the query results.But not sure, how it will help me for my requirement. Could you provide more details? If possible, pls provide a sample query.

Report message to a moderator

Re: SQL Query Suggestion [message #645154 is a reply to message #645153]

Fri, 27 November 2015 11:20

msambasiva
Messages: 4
Registered: November 2015

Junior Member

Got it. Below is the query.
SELECT obsolete FROM
(SELECT obsolete, ROW_NUMBER() OVER (PARTITION BY doc_id ORDER BY obsolete DESC NULLS FIRST) R
FROM objects)
WHERE R = 1

Thank you!!

Report message to a moderator

Previous Topic:	SQL Query Help
Next Topic:	sql queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 00:57:11 CDT 2024