Select Top n rows

Question

I have an SQL result set that looks similar to this, and ordered by STYLE:

STYLE     |     CUSTOMER     |     SIZE     |      COLOUR      |
-----------------------------------------------------------------
A               DWTG               S               BLUE
B               DWTG               S               RED
C               DWTG               S               GREEN
C               DWTG               M               GREEN
D               DWTG               S               ORANGE
D               DWTG               M               ORANGE
D               DWTG               L               ORANGE

Now, I need to split this result set and get the first 1000 rows. No problem, I can use TOP 1000.

My problem is, with the example above, if I wanted, say the top 3 rows, because STYLE has C repeated at the next row, I need to include this as well (so I would actually have 4 rows).

Example above, I would want the result set to look like:

STYLE     |     CUSTOMER     |     SIZE     |      COLOUR      |
-----------------------------------------------------------------
A               DWTG               S               BLUE
B               DWTG               S               RED
C               DWTG               S               GREEN
C               DWTG               M               GREEN

This has come about because an ERP we are importing data into has a file size limit, so I need to split my result set into smaller files, but I need to keep the same records (same value in STYLE) together.

I was thinking a window function of some sort that dynamically adds a number to the TOP n based on if the next row has the same style.

Any ideas?

Answer 1

It sounds like you want this:

select top 3 with ties *
from yourtable
order by style

See SQL Fiddle with Demo

Edit #1: See an updated sql fiddle with your additional data

Result:

| STYLE | CUSTOMER | SIZE | COLOUR |
------------------------------------
|     A |     DWTG |    S |   BLUE |
|     B |     DWTG |    S |    RED |
|     C |     DWTG |    S |  GREEN |
|     C |     DWTG |    M |  GREEN |