Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Subquery in Oracle SQL Statement

Experts,

Does anyone know how to write the following query in Oracle.

i need to select all records where DateA is = 01/31/2013 for MemberA and Member A has no records where DATEA is  greater than 01/31/2013?
0
morinia
Asked:
morinia
1 Solution
 
slightwv (䄆 Netminder) Commented:
I'm thinking this can be simplified but this is off the top of my head:

select * from table where member='MemberA' and dateA = to_date('01/31/2013','MM/DD/YYYY')
and not exists (select 1 from table where member='MemberA' and dateA >= to_date('01/31/2013','MM/DD/YYYY'))
0
 
slightwv (䄆 Netminder) Commented:
Now that I've been trying to simplify it, I'm not sure I'm following.

If memberA has a record with 1/31/2013 and records > 1/31/2013, are you wanting no rows returned?

Can you post some sample data and expected results?
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
File A
    MEMBER      DATEA
   MEMBERA     1/31/2013
   MEMBERB    1/31/2013
   MEMBERB    2/01/2013
   MEMBERC    1/13/2013
   MEMBERD    1/13/2013
   MEMBERD    2/01/3013


With this data, I would like only MEMBERA 1/31/2013 and MEMBERC returned

I don't want to select  MEMBERB AND MEMBERC because they have records with date = 2/01/2013 which is greater than 1/31/2013
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
Not as straightforward, but possibly more efficient since it only needs to read the table once
test on your own system to confirm though


SELECT *
  FROM (SELECT t.*
               , MAX(CASE WHEN datea >= TO_DATE('01/31/2013', 'MM/DD/YYYY') THEN 'Y' END) OVER (partition by member) rejected
          FROM yourtable t   )
 WHERE datea = TO_DATE('01/31/2013', 'MM/DD/YYYY') AND rejected IS NULL
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
select *
  from table a
 where a.date = to_date('01/31/2013', 'mm/dd/yyyy')
   and not exists (select 'X'
          from table b
         where b.member = a.member
           and b.date > a.date);
0
 
ianmills2002Commented:
Another variation you can try is as follows

select member, min(datea), max(datea)
from yourtable
where  datea >= TO_DATE('01/31/2013', 'MM/DD/YYYY')
group by member
having min (datea) = TO_DATE('01/31/2013', 'MM/DD/YYYY')
 and min(datea) = max(datea)
0
 
PaulCommented:
ID: 39254386 needs a small tweak remove = from >=
 , MAX(CASE WHEN datea > TO_DATE('01/31/2013', 'MM/DD/YYYY') THEN 'Y' END) OVER

with that applied 3 existing variants all returning MEMBERA only
(I think there is an inconsistency in wording of the expected results by the way)

all those, plus another below are available for comparison here
http://sqlfiddle.com/#!4/5604f/2

I think ianmills marginally has the lowest cost - but sqlfiddle results don't account for indexes etc. and volume is very low of course

:) my variant
SELECT
  member
, datea
FROM (
      SELECT
        member
      , datea
      , row_number() over (partition BY member ORDER BY datea DESC) AS row_ref
      FROM table1
     )
WHERE row_ref = 1
AND datea = TO_DATE('2013-01-31', 'YYYY-MM-DD')
;

Open in new window

ps: I don't natively use MM/DD/YYYY just seems weird to me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now