Post reply

Select sample data for each value of "group by"

  • December 9, 2015 at 2:31 pm

    #306996

    Hello all,

    I have a table with about a million rows. There is a category column with around 20 different values. How can I produce a sample extract of 100 rows of each of the values in the category column without a cursor? Tried with the "ORDER by... OFFSET ...FETCH x rows" but it does not apply the FETCH to each group.

    Thanks in advance for any help

  • December 9, 2015 at 3:02 pm

    #1845066

    I can't claim to be an expert on the subject, but here's a good article by Itzik Ben-Gan that shows different ways to do it and the performance of each option.

    HTH,

    Pieter

  • December 9, 2015 at 9:53 pm

    #1845094

    Raul Undreiner (12/9/2015)

    Hello all,

    I have a table with about a million rows. There is a category column with around 20 different values. How can I produce a sample extract of 100 rows of each of the values in the category column without a cursor? Tried with the "ORDER by... OFFSET ...FETCH x rows" but it does not apply the FETCH to each group.

    Thanks in advance for any help

    Something like this...

    SELECT

    c.CategoryID,

    c.CategotyName,

    mt.*

    FROM

    dbo.Category c

    CROSS APPLY (

    SELECT TOP 100

    mt.*

    FROM

    dbo.MyTable mt

    WHERE

    c.CategoryID = mt.CategoryID

    ) t100

Viewing 3 posts - 1 through 2 (of 2 total)

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