# Thread: [RESOLVED] select top 100 percent

1. ## [RESOLVED] select top 100 percent

I am looking into a calculated number that my customer has brought to my attention. It is a different number in an asp page than on a report and they are supposed to match, so he asked me to find out how it is derived.

This number comes from a view and the view does a SELECT TOP 100 PERCENT which I have never seen before. I ran the view and it returned 94986 rows. I took the TOP 100 PERCENT out and it returned exactly the same results. I am not sure I understand what this is for. When I googled it I couldn't find anything that was just a basic explanation. What is its purpose when having it and removing it yield the same rows?

Thanks.

2. ## Re: select top 100 percent

I apologize - the Zyrtec my coworker gave me today for my allergies has made me drowsy!

They are the same number of rows but not the same data.

Can you still enlighten me about what the difference is exactly?

Thanks.

3. ## Re: select top 100 percent

Select TOP 100 PERCENT is Equivalent to SELECT *
it would have been more meaningfull if query had value other than 100 PERCENT for e.g if Sales Table has 100 records and you want to know Top 40% sales records order by amount value than SELECT TOP 40 PERCENT FROM Sales Order by Amount would be more meaningfull

4. ## Re: select top 100 percent

The Top clause is required if the View has an Order By clause.

Can you still enlighten me about what the difference is exactly?
It would depend on the SQL statement.

5. ## Re: select top 100 percent

Could you post some example data of what you have and what you need to get from the table...this will help us to understand you clearly...from your thread I have understood that you want to get top some records which are satisfying your calculated value...Am I right?

For example...
SELECT TOP 10
FieldList
FROM TableName
WHERE FieldValue = 100
ORDER BY FieldName ASC;

If you use PERCENT it will limit the number of result records to the specified number unlike just using TOP.

SELECT TOP 10 PERCENT
FieldList
FROM TableName
WHERE FieldValue = 100
ORDER BY FieldName ASC;

Hope it helps you someway...

6. ## Re: select top 100 percent

As Bruce said, the top 100 % allows you to order a view. It has absolutely no effect on the rows being returned. In your case it's a red herring and is not the cause of your differing results.

Views are naturally unordered (exactly as tables are) and you're meant to order when you query from them, rather than when you create them. As such order by is not supported in views.
However, a top X is supported in views but it's pretty meaningless without an order by, so order by is supported when using a top x. This gives rise to the 'trick' of selecting the top 100% when you want to create an ordered view. It doesn't affect the records at all, it merely allows you use an order by where sqlserver wouldn't normally allow you to.

7. ## Re: select top 100 percent

Originally Posted by brucevde
The Top clause is required if the View has an Order By clause.
I am looking at this same bug again and remembered that I had asked this question. The view does have an ORDER BY, so given the above statement, that must be why the TOP 100 PERCENT is there - it's required, and returns all rows.

Thanks for helping me...twice!

8. ## Re: select top 100 percent

Excellent!

As you now have it sorted out (again!), could you please do us a little favour, and mark the thread as Resolved?
(that way other people are more likely to find it too)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured