selecting x records from table n times according to variable criteria?

[M Wells]

Hi All,

Sorry if the subject line is too obscure -- I couldn't think of a way
of describing this request.

I have a table that contains approximately 1 million records.

I want to be able to be able to select the top x records out of this
table matching variable criteria.

Pseudo table records:

custid, category, segment
1,1,1
2,1,1
3,1,1
4,1,1
5,1,2
6,1,2
7,1,2
8,1,2
9,2,1
10,2,1
11,2,1
12,2,1
13,2,2
14,2,2
15,2,2
16,2,2
17,2,3
18,2,3
19,2,3
20,2,3
So, what I'm trying to do is return a recordset, for example, that
contains the top 2 of each variation of category and segment.

ie:

1,1,1
2,1,1
5,1,2
6,1,2
9,2,1
10,2,1
13,2,2
14,2,2
17,2,3
18,2,3

The only way I can think to achieve this is in a while statement,
performing individual selects against each combination, feeding the
where criteria by variables that I automatically increment.

I can't help thinking there's a much more graceful way of achieving
this?

If anyone can give me any insight into this I'd be incredibly
appreciative!

Many thanks in advance!

Much warmth,

Murray

[Dan Guzman]

> So, what I'm trying to do is return a recordset, for example, that

contains the top 2 of each variation of category and segment.
Here's one method, assuming the 'top 2' are the 2 rows with the lowest
custid values and custid is unique within category and segment.

CREATE TABLE MyTable
(
custid int,
category int,
segment int
)
INSERT INTO MyTable
SELECT 1,1,1
UNION ALL SELECT 2,1,1
UNION ALL SELECT 3,1,1
UNION ALL SELECT 4,1,1
UNION ALL SELECT 5,1,2
UNION ALL SELECT 6,1,2
UNION ALL SELECT 7,1,2
UNION ALL SELECT 8,1,2
UNION ALL SELECT 9,2,1
UNION ALL SELECT 10,2,1
UNION ALL SELECT 11,2,1
UNION ALL SELECT 12,2,1
UNION ALL SELECT 13,2,2
UNION ALL SELECT 14,2,2
UNION ALL SELECT 15,2,2
UNION ALL SELECT 16,2,2
UNION ALL SELECT 17,2,3
UNION ALL SELECT 18,2,3
UNION ALL SELECT 19,2,3
UNION ALL SELECT 20,2,3

SELECT
custid,
category,
segment
FROM MyTable a
WHERE
(
SELECT COUNT(*)
FROM MyTable b
WHERE
b.category = a.category AND
b.segment = a.segment AND
b.custid <= a.custid
) <= 2
ORDER BY
category,
segment,
custid

--
Hope this helps.

Dan Guzman
SQL Server MVP

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:km********************************@4ax.com... Hi All,

Sorry if the subject line is too obscure -- I couldn't think of a way
of describing this request.

I have a table that contains approximately 1 million records.

I want to be able to be able to select the top x records out of this
table matching variable criteria.

Pseudo table records:

custid, category, segment
1,1,1
2,1,1
3,1,1
4,1,1
5,1,2
6,1,2
7,1,2
8,1,2
9,2,1
10,2,1
11,2,1
12,2,1
13,2,2
14,2,2
15,2,2
16,2,2
17,2,3
18,2,3
19,2,3
20,2,3
So, what I'm trying to do is return a recordset, for example, that
contains the top 2 of each variation of category and segment.

ie:

1,1,1
2,1,1
5,1,2
6,1,2
9,2,1
10,2,1
13,2,2
14,2,2
17,2,3
18,2,3

The only way I can think to achieve this is in a while statement,
performing individual selects against each combination, feeding the
where criteria by variables that I automatically increment.

I can't help thinking there's a much more graceful way of achieving
this?

If anyone can give me any insight into this I'd be incredibly
appreciative!

Many thanks in advance!

Much warmth,

Murray