0

I am trying to use a sub-query to pick out the first element of a table with some other filtering on it.

I am working based off a comment on this answer to a similar question. My query differs slightly because I am doing some filtering on the table generated from the sub-query.

select *
from (
  select firstname, lastname
  from employees
  order by (lastname)
)
where
  rownum = 1
  AND lastname like :wildcard;

My question is: Is it possible that additional filtering like this will spoil the order from the sub-query, making this technique not work in some cases?

There is an obvious solution to this problem, where you could simply move the lastname like :wildcard filter inside the sub-query, but the application I am working with restricts me from doing that (and I am sorry if that makes the question more obscure).

EDIT:

To try to clarify my application restriction, the lastname like :wildcard filter cannot be in ANY from clause sub-query.

To explain, as best as I can without making this a very complicated question, the application uses pieces of queries in many different places and will edit queries depending on context. Sometimes the application substitutes :wildcard with column references from parent queries. Based on experimentation, this cannot be done in from clause subqueries (though I was unable to find documentation to that effect and I can only guess at why).

Community
  • 1
  • 1
Kevin S.
  • 628
  • 6
  • 9

2 Answers2

0

I don't understand why you'd be unable to alter the inner subquery, but if you really can't, you may have to do multiple subqueries like this:

select *
from (
  select *
  from (
    select firstname, lastname
    from employees
    order by (lastname)
  )
  where lastname like :wildcard
)
where rownum = 1;

The way you have it written, you'll only get the correct results when you query the last name that comes first alphabetically. If you query something like "Smith", you'll get 0 rows returned.

Gurduloo
  • 151
  • 6
  • Thanks for the response, but I can't have the `lastname like :wildcard` filter in ANY `from` clause sub-query. See the edit I made to the question. I apologize for not being clearer with my original question. – Kevin S. Apr 27 '15 at 21:48
  • Maybe you can use a `WITH` clause? Something like this: `WITH t1 AS ( SELECT firstname, lastname FROM employees WHERE lastname like :wildcard ORDER BY (lastname) ) SELECT * FROM t1 WHERE rownum = 1;` – Gurduloo Apr 28 '15 at 12:41
0

I can't think of any issues. You could also use the following statement, which makes it somewhat clearer:

select *
  from (select *
        from (select firstname, lastname
                from (select 'Tim' firstname,'Barton' lastname from dual union 
                      select 'Tom' firstname,'Benton' lastname from dual union
                      select 'Jim' firstname,'Teresi' lastname from dual union
                      select 'Sam' firstname, 'Smith' lastname from dual)
               order by (lastname))
        where lastname like 'Be%')
  where rownum = 1;

Check this demo.

Trinimon
  • 13,839
  • 9
  • 44
  • 60
  • Thanks for the response, your example doesn't help me because I can't have the `lastname like :wildcard` sub-query in ANY `from` clause sub-query. If you're right about there not being any problems then that is Awesome! I am going to give some time for someone to disagree though unless there is some info somewhere that guarantees it. – Kevin S. Apr 27 '15 at 21:54
  • The demo was rather for explanation for why I think that there won't be an issue. As you can see the inner sub select is structurally the same as you have above. It would be a problem if you restrict the results with `rownum` first - compare for instance the two statements in this Fiddle: http://sqlfiddle.com/#!4/9eecb/5634. The first statement doesn't return any rows. However, the statement you showed us above is more or less equivalent to what I wrote in my answer. – Trinimon Apr 28 '15 at 06:45