current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 3 down vote favorite

Essentially I'm attempting to select the top read (based on read_date), and check if it has a type in ( 'R','S','C','B','Q','F','I','A')

select *
from (
        select *
        from (
           select *
           FROM table
           WHERE  id = 369514
           order by read_date desc
            )
         where rownum = 1
         )
 where type IN ( 'R','S','C','B','Q','F','I','A')

I only need to return a row if the top row has a type in the set, if not return nothing.

The only way I seem to be able to is using all these nasty sub-queries.

share|improve this question
    
is this Query slow? is it inefficient? –  Malachi Oct 29 '13 at 13:51
    
not slow, just looked like it could probably be improved –  m.edmondson Oct 29 '13 at 21:06
    
have you tried either answer yet? I am curious...lol –  Malachi Oct 29 '13 at 21:15
    
please accept an answer on your questions. –  Malachi Oct 30 '13 at 14:25

2 Answers 2

up vote 3 down vote accepted

I am not an Oracle SQL person, but I was looking at the Query and think that you should be able to get rid of the outside Select.

select *
from (
      select *
      FROM table
      WHERE  id = 369514
      order by read_date desc
      )
where rownum = 1 AND type IN ( 'R','S','C','B','Q','F','I','A')

I don't have anything to Test against, but it looks like this would work. please correct me if I am wrong.

share|improve this answer
1  
I've actually realised this myself and removed it. I've upvoted :-) –  m.edmondson Oct 29 '13 at 21:24
up vote 0 down vote

You can try this

SELECT
      *
FROM
      (SELECT
            COL1,
            COL2,
            ROW_NUMBER ( )
                OVER (PARTITION BY DEPARTMENT ORDER BY READ_DATE DESC)
                SRLNO
       FROM
            YOURTABLE
       WHERE
            TYPE IN ('R', 'S', 'C', 'B', 'Q', 'F', 'I', 'A')
            AND ID = 369514)
WHERE
      SRLNO = 1;
share|improve this answer
    
Thanks for your comment, however I think this makes the query appear more complicated? –  m.edmondson Oct 29 '13 at 21:25
    
Oracle window functions primarily serves this purpose. But why do you feel the other way? For Example: In the above answer, the inner query picks 10 records for ID 369514, and then outer query picks one out of it. But in my case, I select definitely 10 or less than that of rows, since I use the TYPE and one row from outer query. Now tell me why you feel the other way? –  realspirituals Oct 29 '13 at 23:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.