?
Solved

How to select top 10 rows in table in sybase

Posted on 2009-12-29
5
Medium Priority
?
6,744 Views
Last Modified: 2012-06-27
How to select top 10 rows in table in sybase
0
Comment
4 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26136600
SELECT TOP 10 *
FROM yourTable
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 26136628
SET rowcount 10
SELECT column FROM table


refer

http://www.petefreitag.com/item/59.cfm
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 26138292
We should note that the solution from tigin44 is only available in ASE 12.5.1 and above... although I certainly hope you're using something more recent than that.

We should also note that both solutions are misleading and often will not work - SQL has no reliable row order without an ORDER BY clause. As written, either of these SQL fragments could return a different result each time it is run.

You need an ORDER BY for consistent row ordering. As it stands, if you asked me your question I would have to ask "top 10 according to what?"

You need (ASE 12.5.1+):

SELECT TOP 10 [column list] FROM [table] ORDER BY [column(s)]

or (any version):

set rowcount 10
SELECT [column list] FROM [table] ORDER BY [column(s)]
set rowcount 0


(Don't forget that final "set rowcount 0" - as written, pratima_mcs's answer will leave you in "display only ten rows" mode.)
0
 
LVL 4

Expert Comment

by:jlsilva01
ID: 26139811
Correnting information: The tag TOP was include on Sybase ASE 12.5.3.



Reference material : http://www.rocket99.com/techref/sybase8684.html
/*
Sybase 12.5.3 does have a "top N" feature, for getting top 10 lists (for example)
Method #1: Built-in Top-N, no rank #
*/
-- show top 10

select top 10 gross_sales, ticker
from sales_data
order by mkt_cap desc
go

/*
Method #2: Rowcount Top-N, no rank #
*/

set rowcount 20
go

select gross_sales, ticker
from sales_data
order by mkt_cap desc
go

set rowcount 0
go

/*
Method #3: Rank all rows, display rank #
Works best where there are no duplicate values
If the data being ranked does not contain duplicates, the easiest method is to:
Copy the data into a temporary table with an identity column
Use order by to sort the rows

Note: If there are duplicate values, the rank assigned will be somewhat random.
*/

select rank = identity(10), ticker, gross_sales
into #top
from sales_data
order by gross_sales desc
go


select * from #top
where rank <= 20
go


drop table #top
go


/*
Method #4: Rank all rows, display rank, ties share ranking
Where there may be duplicate values
If the data to be ranked contains duplicates,
Copy the data into a temporary table
Join the temporary table back to the base table
Final select uses rank_id in the WHERE clause.
*/

select rank = identity(10), gross_sales
into #top from sales_data
group by gross_sales
order by gross_sales desc
go


select t1.rank, t2.ticker, t2.gross_sales
from #top t1, sales_data t2
where t1.gross_sales = t2.gross_sales and rank <= 20
order by t2.gross_sales desc
go

drop table #top
go

/*
Method #5: Rank N rows, display rank, ties share ranking
If you want to rank only the top N values, use rowcount on the select into command
*/

set rowcount 20
go

select rank = identity(10), gross_sales
into #top from sales_data
group by gross_sales
order by gross_sales desc
go

set rowcount 0
go

select t1.rank, t2.ticker, t2.gross_sales
from #top t1, sales_data t2
where t1.gross_sales = t2.gross_sales
order by t2.gross_sales desc
go


drop table #top
go


/*
Method #6: Alternate to #4/5
There is an alternate solution for ranking data that contains duplicates:
This method uses a correlated subquery in the select clause
The previous method will probably outperform this method
*/

set rowcount 30
go

select (select count(distinct t2.gross_sales)
from sales_data t2
where t2.gross_sales >= t1.gross_sales) as rank,
t1.ticker, t1.gross_sales
from sales_data t1
where t1.gross_sales is not null
order by rank
go

/*
Method #7: Rank all rows, display rank,
ties share ranking and take up subsequent rankings
"Ranking with Gaps" "Olympic Style"
The previous methods assume that each ordinal
number will be followed by the next number
For example, 6th place would follow 5th place
However, if there are two 5th place entries, you may prefer to follow them with 7th place:
*/

set rowcount 30
go

select (select count(t2.gross_sales) + 1
from sales_data t2
where t2.gross_sales > t1.gross_sales) as rank,
t1.ticker, t1.gross_sales
from sales_data t1
where t1.gross_sales is not null
order by rank
go

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Often times it's very very easy to extend a volume on a Linux instance in AWS, but impossible to shrink it. I wanted to contribute to the experts-exchange community a way of providing a procedure that works on an AWS instance. It can also be used on…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question