First result only from a subquery.
Hi all,
Here's an MS-SQL subquery I need to duplicate under Oracle:
(SELECT TOP 1 AField FROM ATable WHERE (OtherTable.A = A) AND (OtherTable.B = B) AND (OtherTable.C = C)) AS Result
Am I right in assuming that this will be equivalent:
(SELECT AField FROM ATable WHERE ( ROWNUM = 1 ) AND (OtherTable.A = A) AND (OtherTable.B = B) AND (OtherTable.C = C)) AS Result
or should it be:
(SELECT AField FROM Table WHERE (OtherTable.A = A) AND (OtherTable.B = B) AND (OtherTable.C = C) AND (ROWNUM = 1)) AS Result
or is there no difference or will this not work? If the latter, how can I make it work?
A speedy response would be greatly appreciated. :-)
Paul
Here's an MS-SQL subquery I need to duplicate under Oracle:
(SELECT TOP 1 AField FROM ATable WHERE (OtherTable.A = A) AND (OtherTable.B = B) AND (OtherTable.C = C)) AS Result
Am I right in assuming that this will be equivalent:
(SELECT AField FROM ATable WHERE ( ROWNUM = 1 ) AND (OtherTable.A = A) AND (OtherTable.B = B) AND (OtherTable.C = C)) AS Result
or should it be:
(SELECT AField FROM Table WHERE (OtherTable.A = A) AND (OtherTable.B = B) AND (OtherTable.C = C) AND (ROWNUM = 1)) AS Result
or is there no difference or will this not work? If the latter, how can I make it work?
A speedy response would be greatly appreciated. :-)
Paul
MSSQL - SELECT TOP 1 ...
Oracle - SELECT * FROM (SELECT ... ) WHERE ROWNUM = 1
If Your query doesn't have GROUP BY / ORDER BY clause You can make ROWNUM = 1 part of Your original query - otherwise You have to put it outside.
Oracle - SELECT * FROM (SELECT ... ) WHERE ROWNUM = 1
If Your query doesn't have GROUP BY / ORDER BY clause You can make ROWNUM = 1 part of Your original query - otherwise You have to put it outside.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't know MS-SQL, so I don't completely understand the MS-SQL syntax. In pseudo-code, is this what you are trying to do:
select the highest value from fieldA in tableA
where tableA, fieldA = some valueA
and tableA, fieldB = some valueB
and tableA, fieldC = some valueC ?
I also want to give you a couple cautions about Oracle. First, Oracle has no "top" command. Oracle does support "max" (and "min" and other group operators). Second, Oracle does have "rownum", but you have to be *VERY* careful with using that, since it is applied as rows are retrieved (which is often, but not always in the order they were inserted) but *BEFORE* the rows are sorted based on an "order by" clause, or a "max" or other group operator. It is possible in Oracle to use a nested select (a type of sub-query which includes an "order by", or a group operator like: "max") then use rownum in the outer select to retrieve the first (top) row from the nested select.
select the highest value from fieldA in tableA
where tableA, fieldA = some valueA
and tableA, fieldB = some valueB
and tableA, fieldC = some valueC ?
I also want to give you a couple cautions about Oracle. First, Oracle has no "top" command. Oracle does support "max" (and "min" and other group operators). Second, Oracle does have "rownum", but you have to be *VERY* careful with using that, since it is applied as rows are retrieved (which is often, but not always in the order they were inserted) but *BEFORE* the rows are sorted based on an "order by" clause, or a "max" or other group operator. It is possible in Oracle to use a nested select (a type of sub-query which includes an "order by", or a group operator like: "max") then use rownum in the outer select to retrieve the first (top) row from the nested select.
ASKER
Thanks people.
Just to confirm: ATable and OtherTable share columns A, B and C. My main query is on OtherTable. I need to find the record in ATable that has the same values of A, B, and C as in OtherTable and pull the AField from it but occasionally (very rarely) there can be two such records in ATable. Obviously this will cause an error so I must avoid it.
I need to be confident here as this situation might only happen a few times a year and this query may be executed many times per day.
>>Btw. order of predicates doesn't matter, so both of queries are identical. All others (without rownum) will be evaluated, rows will be numbered and then ROWNUM = 1 applied.
I think this answers my question. Am I right?
Paul
Just to confirm: ATable and OtherTable share columns A, B and C. My main query is on OtherTable. I need to find the record in ATable that has the same values of A, B, and C as in OtherTable and pull the AField from it but occasionally (very rarely) there can be two such records in ATable. Obviously this will cause an error so I must avoid it.
I need to be confident here as this situation might only happen a few times a year and this query may be executed many times per day.
>>Btw. order of predicates doesn't matter, so both of queries are identical. All others (without rownum) will be evaluated, rows will be numbered and then ROWNUM = 1 applied.
I think this answers my question. Am I right?
Paul
If Your main concern was if this matters (I mean - if this is evaluated in the moment You expect it to be, after other conditions) - then yes.
General truth about migrating TOP N queries from SQL Server to Oracle is in my first post. Those examples are semantically equivalent.
General truth about migrating TOP N queries from SQL Server to Oracle is in my first post. Those examples are semantically equivalent.
ASKER
Thanks! It worked! :-)
in general there is equal how you will write where clause. Oracle optimiser will choose the best way.
inner brackets are not necessary because you're using AND
a potential problem is that there is no join (if necessary at all) between Table and OtherTable...
Regards
!i!