Post reply

Pulling Top XX rows, where XX is a percentage of records

  • September 25, 2018 at 8:07 am

    #377733

    it never stops around here. . . lol
    My clients brings in between 20,000 and 30,000 SKUs a month.
    He wants me to audit 1% of them.  Literally, 1% each month. I currently use 25. 
    How can i change this code to pull TOP .01 of Count(SKU) where date between, etc.?

    SELECT top 25
    H.SKU
    FROM ADHOC.ATS_ESH
    ORDER BY NEWID()

    Thanks!

  • September 25, 2018 at 8:17 am

    #2006900

    SELECT TOP 1 PERCENT h.SKU
    ...

    John

  • September 25, 2018 at 8:18 am

    #2006901

    SELECT TOP 2 PERCENT :<column list>
    FROM ...

  • September 25, 2018 at 8:28 am

    #2006905

    oh PLEASE dont tell me that it is that easy!  lol
    Thanks so much . . . 
    Next question, where do i go to get my ego back?

    lol

  • September 25, 2018 at 8:49 am

    #2006915

    I don't know, but I know where you can find a lot of ids!

    John

  • September 25, 2018 at 9:03 am

    #2006919

    FOLLOW UP!

    How can i pull .5 percent? 

    I get Conversion failed when converting from a character string to uniqueidentifier.

    thanks again

  • September 25, 2018 at 9:08 am

    #2006922

    You could select TOP 50 PERCENT FROM TOP 1 PERCENT (or any other two numbers that multiply to 0.005).

    John

  • September 25, 2018 at 9:15 am

    #2006925

    thanks, but when tested that with 
    SELECT TOP 50 PERCENT FROM TOP 50 percent

    I get an error near FROM . .

    thoughts? thanks

  • September 25, 2018 at 9:24 am

    #2006929

    SELECT TOP 25 PERCENT x.ColList
    FROM (
       SELECT TOP 2 PERCENT t.ColList
        FROM MyTable t
        ) x
        

    John

  • September 25, 2018 at 9:26 am

    #2006930

    Thanks . . but it appears that i can use decimal percentage . .  i thought i tried this, but now it works.

    Select top .5 percent from . . . .

    thanks though! you guys rock

  • September 26, 2018 at 7:16 am

    #2007070

    John Mitchell-245523 - Tuesday, September 25, 2018 8:49 AM

    I don't know, but I know where you can find a lot of ids!

    John

    Image result for Freud facepalm

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply