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

Question

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

Thanks in advance!

score 2 · Accepted Answer · answered Jun 22 '11 at 09:32

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.

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

1

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

edited Apr 13 '17 at 12:42

Community

1

answered Jun 22 '11 at 09:59

gbn

69,809
8
163
243

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
1

@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

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

2 Answers2

Linked