# “For each group of results fullfilling condition X, select only result with smallest column value A”

I have a table with data and dates like so:

``````DECLARE @p TABLE (P_key int, P_Data char(1), P_ValidUntil datetime)

INSERT @p VALUES (20, 'T', '2003-02-28')
INSERT @p VALUES (21, 'U', '2005-05-31')
INSERT @p VALUES (30, 'V', '2006-09-30')
INSERT @p VALUES (30, 'W', '2008-04-30')
INSERT @p VALUES (31, 'X', '2007-06-30')
INSERT @p VALUES (32, 'Y', '2005-01-31')
INSERT @p VALUES (32, 'Z', '2007-06-30')
INSERT @p VALUES (33, 'A', '2005-06-30')
``````

Given: qKey (only multiples of 10), qDate

Find: All entries that match qKey in the first digit and have a date larger then qDate. Return only one result per P_key (the one with the next higher P_ValidUntil to qDate).

The current solution is:

``````DECLARE @qKey AS int;
DECLARE @qDate AS datetime;

SET @qKey=30;
SET @qDate='2006-01-01';

SELECT * FROM @p WHERE
@qKey = (P_Key/10)*10
AND @qDate <= P_ValidUntil
``````

which returns:

``````30    V    2006-09-30 00:00:00.000
30    W    2008-04-30 00:00:00.000
31    X    2007-06-30 00:00:00.000
32    Z    2007-06-30 00:00:00.000
``````

This is basically correct, except that there are two entries for 30 (both are bigger than @qDate). I only want the smallest date of these multiple hits in the result:

``````30    V    2006-09-30 00:00:00.000
31    X    2007-06-30 00:00:00.000
32    Z    2007-06-30 00:00:00.000
``````

-

A first fix would be:

``````SELECT p2.*
from @p p2
JOIN (
SELECT min(p_validUntil) Min_P_ValidUntil, P_Key
FROM @p p
WHERE @qKey = (P_Key/10)*10
AND @qDate <= P_ValidUntil
group BY P_Key
) p3 ON p3.Min_P_ValidUntil = p2.P_ValidUntil AND p3.P_Key = p2.P_Key
``````

thanks.

-

Use a Window function

``````SELECT
P_Key, P_Data, P_ValidUntil
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY P_Key ORDER BY P_ValidUntil) AS rn
FROM
@p
WHERE
P_Key / @qKey = 1 AND @qDate <= P_ValidUntil
) p2
WHERE
rn = 1
``````

FYI: both techniques used in the 2 answers are quite common because it's a common problem. Examples here in this DBA-SE question: How to get the MAX row

-
Thanks for the info and the link. Unfortunately I find it very hard to search for DB-Related questions, unlike for coding questions. Usually I create my DB-schemas in a way that simple SQL is enough to solve my problems. – B3ret Jun 22 '11 at 12:08
@B3ret: it was really was FYI, not a criticism. The same Q pops up on StackOverflow quite often – gbn Jun 22 '11 at 12:09