Minimum datetime value

Question

Post reply

Minimum datetime value

info 58414

Hall of Fame

Points: 3386
More actions
July 7, 2014 at 2:39 am

#293064

Hi
I am searching for t-sql code which the following criteria:
If Column C3 = 1 then take this row,
else take the row with the minimum datetime value of Column c1
c1c2c3
2014-01-15 2014-10-31 1
2014-11-01 2014-11-30 0
2014-12-01 2015-11-30 0
2015-12-01 2017-11-30 0
2017-12-01 NULL 0
Regards
Nicole
🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 1

CREATE TABLE #Test (c1 datetime, c2 datetime, c3 bit)

INSERT #Test (c1, c2, c3)

VALUES ('2014-01-15', '2014-10-31', 1),

('2014-11-01', '2014-11-30', 0),

('2014-12-01', '2015-11-30', 0),

('2015-12-01', '2017-11-30', 0),

('2017-12-01', NULL, 0);

SELECT*,

CASE c3

WHEN 1 THEN c1

ELSE MIN(c1) OVER (PARTITION BY null)

END

FROM#Test

DROP TABLE #Test

info 58414 Hall of Fame Points: 3386 More actions · Answer 2

Hi,

thank you for the answer.

Is there a way, to select/display only the Record with this Criteria ?

Regards

Nicole

🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SELECT TOP 1 *

FROM (

SELECT *, rn = ROW_NUMBER() OVER(ORDER BY c1)

FROM #Test

) d

ORDER BY CASE WHEN c3 = 1 THEN 0 ELSE 1 END, rn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

info 58414 Hall of Fame Points: 3386 More actions · Answer 4

info 58414

Hall of Fame

Points: 3386

July 7, 2014 at 6:00 am

#1727320

GREAT ! 😛

Thank you

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

ChrisM@Work (7/7/2014)
SELECT TOP 1 * FROM ( SELECT *, rn = ROW_NUMBER() OVER(ORDER BY c1) FROM #Test ) d ORDER BY CASE WHEN c3 = 1 THEN 0 ELSE 1 END, rn

Why does it need the ROW_NUMBER? Aren't you just doing: -

SELECT TOP 1

[c1],

[c2],

[c3]

FROM #Test

ORDER BY CASE WHEN c3 = 1 THEN 0

ELSE 1

END, c1;

?

I'd have thought that would be faster as well, although I'm only guessing.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Cadavre (7/7/2014)
ChrisM@Work (7/7/2014)
SELECT TOP 1 * FROM ( SELECT *, rn = ROW_NUMBER() OVER(ORDER BY c1) FROM #Test ) d ORDER BY CASE WHEN c3 = 1 THEN 0 ELSE 1 END, rn
Why does it need the ROW_NUMBER? Aren't you just doing: -
SELECT TOP 1
[c1],
[c2],
[c3]
FROM #Test
ORDER BY CASE WHEN c3 = 1 THEN 0
ELSE 1
END, c1;
?
I'd have thought that would be faster as well, although I'm only guessing.

You're absolutely right, of course. On both counts. The difference could be significant too - the two sorts IIRC were each around 44% of total cost. Not sure what I was thinking - except perhaps the scenario was too simplified to be realistically representative of the problem.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden