Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help (Oracle 10G)
SQL Query Help [message #436421] Wed, 23 December 2009 08:25 Go to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi All,
I have a requirement where i need to provide a SQL statement
to front application.

 The data is like 
        Login_id     Login_group     Expiry_date
          100             1            23-DEC-2010
          101             1            23-DEC-2010
          102             1            26-NOV-2010
          10001           2            23-DEC-2010
          10002           2            23-DEC-2010
          10003           3            23-DEC-2010
          10004           4            23-DEC-2010
  
 The Result should be like that 
          Login_id     Login_group     Expiry_date
          10001           2            23-DEC-2010
          10002           2            23-DEC-2010
          10003           3            23-DEC-2010
          10004           4            230DEC-2010


Only those records in a Single ( Login_group ) should return
where all Login_id are expiry on 23-DEC-2010

The Login_id 100,101, 102 should all exclude from the SQL result as 102 is not Expiry on 23-DEC-2009 so all Records with login_group 1 are Excluded.

In Short Only those records should be return where all records in same Login_group are Expired on 23-DEC-2010.

Any hint is Welcome.

Thanks
Re: SQL Query Help [message #436422 is a reply to message #436421] Wed, 23 December 2009 08:28 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Build a query that will contain the ids of any group that contains a date that is not the 23rd and use that in a subquery in the where clause:
select cols
from tab
where group not in (your subquery)
Re: SQL Query Help [message #436430 is a reply to message #436421] Wed, 23 December 2009 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you post such a question always a workingTest case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: SQL Query Help [message #436433 is a reply to message #436421] Wed, 23 December 2009 09:38 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Alternatively, use analytic functions in order to avoid the subquery and maybe improve performances.
Re: SQL Query Help [message #436501 is a reply to message #436421] Wed, 23 December 2009 19:28 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
LAST_VALUE(Login_group) with windowing clause having specified expiry_date.

regards,
Delna
Previous Topic: Ignore ORA-01427: single-row subquery returns more than one row
Next Topic: outer join and exsists
Goto Forum:
  


Current Time: Thu Dec 08 22:11:03 CST 2016

Total time taken to generate the page: 0.08877 seconds