<div>
&gt;&gt; but it will not return records in the order they were inserted into the DB.<br />
It will return records in the descending order of col1. 
</div>


<div>
&gt;but it will not return records in the order they were inserted into the DB.<br />
to get such an &quot;order&quot;, you have to have a column that stores the inserted &quot;order&quot; (eventually time).<br />
unless you have such a column, you cannot get the &quot;insertion order&quot; info back.
</div>


<div>
forget the last line of my question.
</div>


<div>
-- Oracle, select bottom 1/3 of rows (based on database arbitrary order)<br />
<br />
select * from (<br />
&nbsp; select t.*, rownum as rownum_ from t<br />
)<br />
where rownum_ between (select (count(1)/3)*2 from t) and (select count(1) from t)<br />

</div>


<div>
Assuming your last comment means &quot;select top 100 * from table1 order by col1 desc&quot; is fine then the last 100 would simply be:<br />
<br />
select top 100 * from table1 order by col1 asc<br />
<br />
The middle 100 is much harder...
</div>


<div>
Or this for Sybase, in 1 swoop if you know how many you want (or combine wth wilcoxon's above)<br />
<br />
select top 10 * from ( select top 20 * from customers order by id desc) t order by id asc<br />

</div>


<div>
- this is quite similar to a recent question on last record inserted to the table here &gt;&nbsp; <br />
<a href="https://www.experts-exchange.com/questions/27036104/Find-out-when-the-last-insert-in-a-table-occured.html" rel="ugc">https://www.experts-exchange.com/questions/27036104/Find-out-when-the-last-insert-in-a-table-occured.html</a><br />
<br />
- you will find the discussions on few options available in Oracle to generate query on getting the top, last and middle record from a table such as timestamp column (by using trigger/setting sysdate as default value), ORA_ROWSCN , logminer, flashback and etc. you can read more on these options in Oracle doc.<br />
<br />
- depending on what option you already have from the above, this is the basic query you can use:<br />
<br />
:last (using MAX to get last record inserted)<br />
SELECT MAX(timestampcolumn/ORA_RO<wbr />WSCN) FROM tablename<br />
<br />
:top (using ascending and rownum to get the first record inserted)<br />
SELECT * FROM<br />
(SELECT timestampcolumn/ORA_ROWSCN<wbr /> FROM tablename ORDER by timestampcolum ASC)<br />
WHERE rownum = 1<br />
<br />
<br />
<br />

</div>


<div>
I need this one query such that it resturns the first 100the last 100 in one recordset.
</div>


<div>
<a href="http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/10327;pt=10792" rel="ugc">http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/10327;pt=10792</a><br />
<br />
You can use order by and compute clauses only at the end of the union statement to define the order of the final results or to compute summary values. You cannot use them within the individual queries that make up the union statement. 
</div>


<div>
In Sybase, you can just include both statements in a single call to get the results in a single record set. &nbsp;However, only Sybase and MS SQL support this behavior so it won't work in Oracle.
</div>


<div>
&quot;You can use order by and compute clauses only at the end of the union statement to define the order of the final results or to compute summary values&quot;<br />
- same goes with Oracle. i dont think you can use union on this type of query.<br />
<br />
- if you are not concern on the date&amp;time then you can use rownum in Oracle. both run individually though:<br />
<br />
: bottom100: sort descedingly and take the first 100<br />
SELECT * FROM tablename <br />
WHERE rownum &lt;= 100<br />
ORDER BY columnname DESC<br />
<br />
: top 100: sort ascendingly and take first 100<br />
SELECT * FROM tablename <br />
WHERE rownum &lt;= 100<br />
ORDER BY columnname ASC<br />
<br />
or just to take any first 100:<br />
SELECT * FROM tablename <br />
WHERE rownum &lt;= 100
</div>


<div>
Unable to post any longer than a line.
</div>


<div>
The 'top 10' in sybase and 'Where ROWNUM&lt;=10' is returning different set of records. Please note that the 2 DBs are same.
</div>


<div>
Queries for both DBs should return the same set of records.
</div>


<div>
- i did not know how TOP works in Sybase. <br />
- ROWNUM in Oracle returns a number indicating the order when Oracle selects the row from the table, i believe by the way they are stored. the first row fetched will be assigned as ROWNUM 1 and so on. the value of ROWNUM is not fixed and assigned to a row after a WHERE clause if defined. it will ignore GROUP BY, HAVING and ORDER BY. so sorting will have no changes to ROWNUM but using WHERE will change the ROWNUM value assignment to the rows. <br />
- if you are willing to do further test, create a new table in Sybase and Oracle then insert 10 records in the same sequence and see if TOP and ROWNUM will return the same result. if TOPS works as ROWNUM though.
</div>


<div>
there are 2 DBs. One sybase second oracle. They both have the same tables within them.<br />
Now i need a query that returns the SAME n records from a given table from both the databases. One query for sybase one for oracle.
</div>


<div>
This is sampling excercise where i get a sample size of n records from each database and do a compare.
</div>


<div>
The attached is for Oracle and assumes n=30, which returns the first 30, the middle 30, and the last 30 records. Just replace 30 with whatever value you need.<br />
<a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/459658/query.txt" target="_blank" class="file-inline" title="Query to retrieve first, last, and middle records (267 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>query.txt</a>
</div>


Query to retrieve first, last, and middle records

query.txt

<div>
The &quot;select top 10&quot; method should work in both Sybase and Oracle and should return the same results given an &quot;order by&quot; clause.
</div>


<div>
wilcoxon,<br />
There is no TOP command in Oracle.
</div>


<div>
awking00, you are correct. &nbsp;I was mistaken in thinking top was a sql standard (Sybase is usually one of the last to add a sql standard so I was assuming Oracle supported top (but it's not standard)).<br />
<br />
It looks like SQL 2008 added a standard equivalent to top n (FETCH FIRST n ROWS ONLY) but neither Sybase nor Oracle support it (yet).
</div>


<div>
&quot;Select top 10&quot; does not work in oracle but works in sybase.
</div>


<div>
After doing some reading on Oracle syntax, I believe these should be equivalent queries:<br />
<br />
Sybase:<br />

<pre><code class="code-10 nolinks" id="code-8168895"><span>-- first 100</span>
<span>select top 100 * from table1 order by col1 asc</span>
<span></span>
<span>-- last 100</span>
<span>select top 100 * from table1 order by col1 desc</span>
<span></span>
<span>-- middle 100</span>
<span>-- not positive which of these will work as I'm not sure how open top is</span>
<span>-- to @vars or sub-queries to define amount</span>
<span>select top 100 *</span>
<span>from (select top (select count(*)/2-50 * from table1 order by col1 desc) t</span>
<span>order by col1 asc</span>
<span></span>
<span>-- or</span>
<span>declare @cnt int</span>
<span>select @cnt = count(*)  from table1</span>
<span>select @cnt = @cnt / 2</span>
<span>select @cnt = @cnt + 50</span>
<span>select top @cnt * into #tmp from table1 order by col1 desc</span>
<span>select top 100 from #tmp order by col1 asc</span>
</code></pre>
<br />
Oracle:<br />
&nbsp;
<pre><code class="code-10 nolinks" id="code-8168875"><span>-- first 100</span>
<span>SELECT * FROM (</span>
<span>  SELECT</span>
<span>    ROW_NUMBER() OVER (ORDER BY col1 ASC) AS rn,</span>
<span>    *</span>
<span>  FROM table1</span>
<span>)</span>
<span>WHERE rn &lt; 100</span>
<span></span>
<span>-- last 100</span>
<span>SELECT * FROM (</span>
<span>  SELECT</span>
<span>    ROW_NUMBER() OVER (ORDER BY col1 DESC) AS rn,</span>
<span>    *</span>
<span>  FROM table1</span>
<span>)</span>
<span>WHERE rn &lt; 100</span>
<span></span>
<span>-- middle 100</span>
<span>SELECT * FROM (</span>
<span>  SELECT</span>
<span>    ROW_NUMBER() OVER (ORDER BY col1 ASC) AS rn,</span>
<span>    *</span>
<span>  FROM table1</span>
<span>)</span>
<span>WHERE rn &gt; (select count(*)/2-50 from table1)</span>
<span>AND rn &lt;= (select count(*)/2+50 from table1)</span>
</code></pre>
 
</div>


<div>
wilcoxon was brave enough to try the Oracle, so I'll try the Sybase. Again, I have used 30 as the assumed n value.<br />
<a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/459758/query.txt" target="_blank" class="file-inline" title="Oracle and Sybase(?) queries (531 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>query.txt</a>
</div>


<div>
@wilcoxon your query to get top n in oracle is returning a missing expression error
</div>


<div>
Which of the three queries is returning the missing expression error?
</div>


<div>
It should work. &nbsp;Are you trying to run the whole block as one query? &nbsp;It is written as three queries (first, last, middle) so should work if you are just running one of the three.
</div>


<div>
When you run wilcoxon's Sybase queries and my Oracle queries, do you get results that are totally different, only somewhat different, or the same? I suspect somewhat different and, if that is the case, post the results and we'll see if we can't tweak our queries to match exactly.
</div>


<div>
See attached.<br />
<a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/460071/questions-and-comments.txt" target="_blank" class="file-inline" title="Questions and comments re: Sybase vs. Oracle queries (848 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>questions-and-comments.txt</a>
</div>


Questions and comments re: Sybase vs. Oracle queries

questions-and-comments.txt

<div>
What two queries did you finally end up with that gave you the same results in Sybase and Oracle?
</div>


<div>
<div class="content wysiwyg-content">
I don't mind if the queries are different for oracle and sybase. The value of n will always be less than 1/3rd of the rowcount (n&lt;1/3rowcount) of the table.<br />
I have considered the following<br />
select top 100 * from table1 order by col1 desc<br />
but it will not return records in the order they were inserted into the DB.<br />

</div>
</div>


I don't mind if the queries are different for oracle and sybase. The value of n will always be less than 1/3rd of the rowcount (n<1/3rowcount) of the table.
I have considered the following
select top 100 * from table1 order by col1 desc
but it will not return records in the order they were inserted into the DB.


Get the top n, middle n and last n records from syabse and oracle DB

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

Sybase, a subsidiary of SAP, builds a client/server relational database management system. Products include Adaptive Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase Unwired Platform (SUP) for mobile applications, Afaria for enterprise mobile device management and IQ for data warehouse and big data applications.

Sybase Database

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.