dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] select top 100 percent

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Resolved [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. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    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. #3
    Frenzied Member
    Join Date
    Jan 2006
    Posts
    1,875

    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
    __________________
    Rate the posts that helped you

  4. #4
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    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.
    Last edited by brucevde; Jul 7th, 2008 at 04:17 PM.

  5. #5
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    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...
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,347

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: select top 100 percent

    Quote 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. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,171

    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


Click Here to Expand Forum to Full Width