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

## All we need is an easy explanation of the problem, so here it is.

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
``````

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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.

### Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0