How can I restrict row number of a union query result?

[mackpei]

Gurus,<br><br>I have following SQL statement:<br><br>SELECT A, B, C<br>FROM T, S<br>WHERE ...<br>UNION<br>SELECT A, B, C<br>FROM T, P<br>WHERE ...<br><br>and I just need the first 50 rows as result.<br><br>How can I do this?<br><br>TIA<br>Mack

 

[stoleru]

SELECT * FROM<br>(SELECT A, B, C<br>FROM T, S<br>WHERE ...<br>UNION<br>SELECT A, B, C<br>FROM T, P<br>WHERE ...)<br>WHERE rownum&lt;=50; but you don't know which 50 rows will be retrieved. You can refine your query an other way.<br> <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>

 

[Invicta]

The SQL Server version is easier...<br><br>SELECT TOP 50 A, B, C ....

 

[stoleru]

My answer was based on Oracle. I don't deny that, in MS SQL, TOP command works (perhaps) faster. But, you see, the three little dots you've used replace the long subquery needed for combining the two select's. So, how do you define <b>easy</b>? <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>

 

[mackpei]

Thanks for your replies.<br><br>I think stoleru's solution works fine for me. It doesn't matter which 50 rows from the<br>two SELECT queries will be retrieved. I can add an ORDER BY:<br><br>SELECT A, B, C FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT A, B, C<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM T, S<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE ...<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT A, B, C<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM T, P<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE ...)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE rownum&lt;=50 ORDER BY A<br><br>Note that the statement used in my case is more complicated than the one given<br>here, I have to write all attributes (A, B, C) instead of a single *, otherwise I get<br>an error:<br><br>Error: ORA-00918: column ambiguously defined<br><br>Mack

 

[Invicta]

Point noted Eduard.<br><br>I meant more natural as a statement, maybe I'm showing a little bias &lt;g&gt;.