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

PearlJamFanatic
PearlJamFanatic used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer

Commented:
>> 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.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
forget the last line of my question.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
-- 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...
I think this will do the middle 100.  I'm not 100% sure "select top @cnt" will work but the logic is sound...

-- you can combine some of these - I just kept them separate for clarity
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
Top Expert 2009

Commented:
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
Top Expert 2011

Commented:
- this is quite similar to a recent question on last record inserted to the table here >  
http://www.experts-exchange.com/Database/Oracle/Q_27036104.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



Author

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

Author

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

Author

Commented:
dfdfdf
Top Expert 2011

Commented:
"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

Author

Commented:
ffgfgfgfggfgfg

Author

Commented:
i am unable to post

Author

Commented:
Unable to post any longer than a line.

Author

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

Author

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

Author

Commented:
need this urgently
Top Expert 2011

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

Author

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

Author

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

Commented:
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.
awking00Information Technology Specialist

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

Author

Commented:
"Select top 10" does not work in oracle but works in sybase.
It looks like the equivalent to:

select top 100 * from table1 order by col1 asc

in Oracle is:

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY col1 ASC) AS ranking,
    *
  FROM table1
)
WHERE ranking <= 100

However, I don't think it's exact.  If I'm reading it right, then the Oracle query will return however many rows match the "top" 100 values so if col1 has duplicates, it will return more than 100 rows.
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

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

Open in new window

awking00Information Technology Specialist

Commented:
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

Author

Commented:
@wilcoxon your query to get top n in oracle is returning a missing expression error
awking00Information Technology Specialist

Commented:
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.
awking00Information Technology Specialist

Commented:
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.
awking00Information Technology Specialist

Commented:
awking00Information Technology Specialist

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial