Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.