• Note that Jeff's second solution will sort the results in the order you demonstrated in your original post. The NTILE solution would not. If you need the numbers in that order then use the second solution (where RNum = Seq%5+1) you could do so using NTILE like this.

    WITH cteEnumerate AS

    (

    SELECT Seq = ROW_NUMBER() OVER (ORDER BY queryName)-1

    ,queryID

    ,queryName

    FROM #TestTable

    )

    SELECT queryID

    ,queryName

    ,RNum = Seq%5+1 -- leaving here to compare to NTILE

    ,NTILE(5) OVER (ORDER BY Seq%5)

    FROM cteEnumerate

    ORDER BY Seq

    I'm not recommending this, I'm just showing how you'd get the result you demonstrated using NTILE. Either way, I would recommend the second solution as it will get the job done much faster and with a lot less reads.

    Lastly, I recommend never using NTILE unless you need:

    1. the results sorted in the in the order that Jeff's above NTILE solution sorts the numbers AND

    2. You need to partition your "tile" numbers (e.g. NTILE(N) OVER (PARTITION BY...))

    If #1 is true and #2 is not (no partitioning required) then I would recommend the NTally table alternative here.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001