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

>> but it will not return records in the order they were inserted into the DB.
It will return records in the descending order of col1.

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

forget the last line of my question.

-- Oracle, select bottom 1/3 of rows (based on database arbitrary order)

select * from (
  select t.*, rownum as rownum_ from t
)
where rownum_ between (select (count(1)/3)*2 from t) and (select count(1) from t)

Assuming your last comment means "select top 100 * from table1 order by col1 desc" is fine then the last 100 would simply be:

select top 100 * from table1 order by col1 asc

The middle 100 is much harder...

Or this for Sybase, in 1 swoop if you know how many you want (or combine wth wilcoxon's above)

select top 10 * from ( select top 20 * from customers order by id desc) t order by id asc

- this is quite similar to a recent question on last record inserted to the table here >  
https://www.experts-exchange.com/questions/27036104/Find-out-when-the-last-insert-in-a-table-occured.html

- 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.

- depending on what option you already have from the above, this is the basic query you can use:

:last (using MAX to get last record inserted)
SELECT MAX(timestampcolumn/ORA_ROWSCN) FROM tablename

:top (using ascending and rownum to get the first record inserted)
SELECT * FROM
(SELECT timestampcolumn/ORA_ROWSCN FROM tablename ORDER by timestampcolum ASC)
WHERE rownum = 1

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

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/10327;pt=10792

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.

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

dfdfdf

"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"
- same goes with Oracle. i dont think you can use union on this type of query.

- if you are not concern on the date&time then you can use rownum in Oracle. both run individually though:

: bottom100: sort descedingly and take the first 100
SELECT * FROM tablename 
WHERE rownum <= 100
ORDER BY columnname DESC

: top 100: sort ascendingly and take first 100
SELECT * FROM tablename 
WHERE rownum <= 100
ORDER BY columnname ASC

or just to take any first 100:
SELECT * FROM tablename 
WHERE rownum <= 100

ffgfgfgfggfgfg

i am unable to post

Unable to post any longer than a line.

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

Queries for both DBs should return the same set of records.

need this urgently

- i did not know how TOP works in Sybase. 
- 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. 
- 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.

there are 2 DBs. One sybase second oracle. They both have the same tables within them.
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.

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

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.
query.txt

The "select top 10" method should work in both Sybase and Oracle and should return the same results given an "order by" clause.

wilcoxon,
There is no TOP command in Oracle.

awking00, you are correct.  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)).

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).

"Select top 10" does not work in oracle but works in sybase.

After doing some reading on Oracle syntax, I believe these should be equivalent queries:

Sybase:

-- first 100
select top 100 * from table1 order by col1 asc

-- last 100
select top 100 * from table1 order by col1 desc

-- middle 100
-- not positive which of these will work as I'm not sure how open top is
-- to @vars or sub-queries to define amount
select top 100 *
from (select top (select count(*)/2-50 * from table1 order by col1 desc) t
order by col1 asc

-- or
declare @cnt int
select @cnt = count(*)  from table1
select @cnt = @cnt / 2
select @cnt = @cnt + 50
select top @cnt * into #tmp from table1 order by col1 desc
select top 100 from #tmp order by col1 asc

Select allOpen in new window

Oracle:
 

-- first 100
SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY col1 ASC) AS rn,
    *
  FROM table1
)
WHERE rn < 100

-- last 100
SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY col1 DESC) AS rn,
    *
  FROM table1
)
WHERE rn < 100

-- middle 100
SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY col1 ASC) AS rn,
    *
  FROM table1
)
WHERE rn > (select count(*)/2-50 from table1)
AND rn <= (select count(*)/2+50 from table1)

Select allOpen in new window

wilcoxon was brave enough to try the Oracle, so I'll try the Sybase. Again, I have used 30 as the assumed n value.
query.txt

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

Which of the three queries is returning the missing expression error?

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

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.

See attached.
questions-and-comments.txt

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