Max value in Case Statements

Question

Post reply

Max value in Case Statements

hegdesuchi

Hall of Fame

Points: 3047
More actions
April 19, 2016 at 10:37 pm

#313425

I have a simple logic but i am missing something in below query.
For all the records with Max(column), set the flag to 1 else 0. how can i do this in Case statement?
For Example,
Table 1
ID Date
1 1/1/1900
2 1/1/1900
3 getdate()
4 Getdate()
Now I want to set a flag and for all the ID's with max date i.e. getdate() in this example but it could be anything, it should be 1 else 0.
any thoughts would be appreciated.

Viewing 15 posts - 1 through 15 (of 19 total)

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

reinhardcilliers SSC Eights! Points: 891 More actions · Answer 1

Hi

When you are referring to max date should the dates all match including the hours, mins and seconds?

If not why just not get the max date form you date cast it as a date and retrieve all dates from your table also cast as a date then you will sit with all the max dates for example 2016-04-20. Then just update their flag to 1 and rest to 0?

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

hegdesuchi (4/19/2016)
I have a simple logic but i am missing something in below query.
For all the records with Max(column), set the flag to 1 else 0. how can i do this in Case statement?
For Example,
Table 1
ID Date
1 1/1/1900
2 1/1/1900
3 getdate()
4 Getdate()
Now I want to set a flag and for all the ID's with max date i.e. getdate() in this example but it could be anything, it should be 1 else 0.
any thoughts would be appreciated.

Quite few ways of doing this, here is one

😎

USE TEEST;

GO

;WITH Table1(ID,Date) AS

(SELECT ID,Date FROM (

VALUES

(1 ,'1/1/1900')

,(2 ,'1/1/1900')

,(3 ,getdate() )

,(4 ,Getdate() )

) AS X(ID,Date))

SELECT

T1.ID

,T1.Date

,CASE

WHEN DENSE_RANK() OVER

(

ORDER BY T1.Date DESC

) = 1 THEN 1

ELSE 0

END AS MAX_DATE_FLAG

FROM Table1 T1;

Output

ID Date MAX_DATE_FLAG

----------- ----------------------- -------------

3 2016-04-20 09:42:37.083 1

4 2016-04-20 09:42:37.083 1

2 1900-01-01 00:00:00.000 0

1 1900-01-01 00:00:00.000 0

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

A different version.

WITH Table1(ID,Date) AS

(SELECT ID,Date FROM (

VALUES

(1 ,'1/1/1900')

,(2 ,'1/1/1900')

,(3 ,getdate() )

,(4 ,Getdate() )

) AS X(ID,Date))

SELECT

T1.ID

,T1.Date

,CASE

WHEN MAX(T1.Date) OVER() = T1.Date THEN 1

ELSE 0

END AS MAX_DATE_FLAG

FROM Table1 T1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 4

Thank you very much for your replies.

Actually i wanted Case statement and then to update the columns .

Will try them.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

Here's an UPDATE version:

IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL

DROP TABLE #test;

CREATE TABLE #test

(

Id INT PRIMARY KEY

,dt DATE

,IsMax BIT

);

INSERT #test

(Id, dt)

VALUES (1, '19000101'),

(2, '19000101'),

(3, '19800101'),

(4, '19900101');

UPDATE #test

SET IsMax = IIF(dt = (SELECT MAX (dt) FROM #test

), 1, 0);

SELECT *

FROM #test t;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

Luis Cazares (4/20/2016)
A different version.
WITH Table1(ID,Date) AS
(SELECT ID,Date FROM (
VALUES
(1 ,'1/1/1900')
,(2 ,'1/1/1900')
,(3 ,getdate() )
,(4 ,Getdate() )
) AS X(ID,Date))
SELECT
T1.ID
,T1.Date
,CASE
WHEN MAX(T1.Date) OVER() = T1.Date THEN 1
ELSE 0
END AS MAX_DATE_FLAG
FROM Table1 T1;

Window Aggregates (without framing) are evil.

SET STATISTICS IO ON;

PRINT 'Win Agg:';

WITH Table1(ID,Date) AS

(SELECT ID,Date FROM (

VALUES

(1 ,'1/1/1900')

,(2 ,'1/1/1900')

,(3 ,getdate() )

,(4 ,Getdate() )

) AS X(ID,Date))

SELECT

T1.ID

,T1.Date

,CASE

WHEN MAX(T1.Date) OVER() = T1.Date THEN 1

ELSE 0

END AS MAX_DATE_FLAG

FROM Table1 T1;

PRINT 'Old School Group By:';

WITH Table1(ID,Date) AS

(SELECT ID,Date FROM (

VALUES

(1 ,'1/1/1900')

,(2 ,'1/1/1900')

,(3 ,getdate() )

,(4 ,Getdate() )

) AS X(ID,Date))

SELECT

T1.ID

,T1.Date

,CASE WHEN t1.Date = x.md THEN 1 ELSE 0 END

FROM Table1 T1

CROSS APPLY

(

SELECT MAX(t1.Date) FROM Table1 t1

) x(md);

SET STATISTICS IO OFF;

Results:

Win Agg:

Table 'Worktable'. Scan count 3, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Old School Group By:

Learned that here[/url].

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 7

Just to make that a little fairer (against an actual table the old-school one will of course have to do more than 0 reads :-)):

SET NOCOUNT ON;

CREATE TABLE #temp (ID int, [Date] DATE);

INSERT INTO #temp VALUES

(1 ,'1/1/1900')

,(2 ,'1/1/1900')

,(3 ,getdate() )

,(4 ,Getdate() );

SET STATISTICS IO ON;

PRINT 'Win Agg:'

SELECT

T1.ID

,T1.Date

,CASE

WHEN MAX(T1.Date) OVER() = T1.Date THEN 1

ELSE 0

END AS MAX_DATE_FLAG

FROM #temp T1;

PRINT 'Old School Group By:';

SELECT

T1.ID

,T1.Date

,CASE WHEN t1.Date = x.md THEN 1 ELSE 0 END

FROM #temp T1

CROSS APPLY

(

SELECT MAX(t1.Date) FROM #temp t1

) x(md);

PRINT 'Rank:';

WITH ranked AS

(

SELECT RNK=RANK() OVER (ORDER BY [Date] DESC),

*

FROM #temp

)

SELECT ID,

[Date],

CASE WHEN RNK=1 THEN 1 ELSE 0 END

FROM ranked

ORDER BY ID;

SET STATISTICS IO OFF;

DROP TABLE #temp;

Results:

Win Agg:

Table 'Worktable'. Scan count 3, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#temp'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Old School Group By:

Table '#temp'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Rank:

Table '#temp'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The windowed aggregate remains quite bad, of course. If we really want to be miserly with IO, though, use RANK 🙂

Cheers!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Just to add timing to the performance tests.

CREATE TABLE temp (ID int, [Date] DATE);

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E4, E2

)

INSERT INTO temp

SELECT n, CAST( CAST( (RAND(CHECKSUM(NEWID())) * 2000) AS int) AS datetime) + '2010'

FROM cteTally

DECLARE @ID int, @Date datetime, @bit bit;

PRINT '--------------------------------------------------------------------------------';

PRINT 'Win Agg:'

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT

@ID = T1.ID

,@Date = T1.Date

,@bit = CASE

WHEN MAX(T1.Date) OVER() = T1.Date THEN 1

ELSE 0

END

FROM temp T1;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

PRINT '--------------------------------------------------------------------------------';

PRINT 'Old School Group By:';

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT

@ID = T1.ID

,@Date = T1.Date

,@bit = CASE WHEN t1.Date = x.md THEN 1 ELSE 0 END

FROM temp T1

CROSS APPLY

(

SELECT MAX(t1.Date) FROM temp t1

) x(md);

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

PRINT '--------------------------------------------------------------------------------';

PRINT 'Rank:';

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH ranked AS

(

SELECT RNK=RANK() OVER (ORDER BY [Date] DESC),

*

FROM temp

)

SELECT @ID = ID,

@Date = [Date],

@bit = CASE WHEN RNK=1 THEN 1 ELSE 0 END

FROM ranked

ORDER BY ID;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

GO

DROP TABLE temp;

(1000000 row(s) affected)

--------------------------------------------------------------------------------

Win Agg:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 102 ms.

Table 'Worktable'. Scan count 3, logical reads 2860396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'temp'. Scan count 1, logical reads 1977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3495 ms, elapsed time = 3758 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Old School Group By:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 6 ms, elapsed time = 6 ms.

Table 'temp'. Scan count 10, logical reads 3954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1452 ms, elapsed time = 581 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Rank:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'temp'. Scan count 5, logical reads 1977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 6363 ms, elapsed time = 3908 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246986 More actions · Answer 9

Same again, but with my SELECT MAX() solution tagged on to the end.

CREATE TABLE temp (ID INT, Date DATE);

WITH E(n)

AS (SELECT E.n

FROM ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0) ) E (n)

),

E2(n)

AS (SELECT a.n

FROM E a

, E b

),

E4(n)

AS (SELECT a.n

FROM E2 a

, E2 b

),

cteTally(n)

AS (SELECT TOP 1000000

ROW_NUMBER() OVER (ORDER BY (SELECT NULL

)) n

FROM E4

, E2

)

INSERT INTO temp

SELECT cteTally.n

, CAST(CAST((RAND(CHECKSUM(NEWID())) * 2000) AS INT) AS DATETIME) + '2010'

FROM cteTally;

DECLARE @ID INT

, @Date DATETIME

, @bit BIT;

PRINT '--------------------------------------------------------------------------------';

PRINT 'Win Agg:';

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT @ID = T1.ID

, @Date = T1.Date

, @bit = CASE WHEN MAX(T1.Date) OVER () = T1.Date THEN 1

ELSE 0

END

FROM temp T1;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

PRINT '--------------------------------------------------------------------------------';

PRINT 'Old School Group By:';

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT @ID = t1.ID

, @Date = t1.Date

, @bit = CASE WHEN t1.Date = x.md THEN 1

ELSE 0

END

FROM temp t1

CROSS APPLY (SELECT MAX(t1.Date)

FROM temp t1

) x (md);

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

PRINT '--------------------------------------------------------------------------------';

PRINT 'Rank:';

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH ranked

AS (SELECT RNK = RANK() OVER (ORDER BY Date DESC)

, *

FROM temp

)

SELECT @ID = ranked.ID

, @Date = ranked.Date

, @bit = CASE WHEN ranked.RNK = 1 THEN 1

ELSE 0

END

FROM ranked

ORDER BY ranked.ID;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

PRINT '--------------------------------------------------------------------------------';

PRINT 'SELECT MAX SUBQUERY:';

PRINT '--------------------------------------------------------------------------------';

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT @ID = t1.ID

, @Date = t1.Date

, @bit = IIF(t1.Date = (SELECT MAX (date) FROM temp), 1, 0)

FROM temp t1

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

GO

DROP TABLE temp;

(1000000 row(s) affected)

--------------------------------------------------------------------------------

Win Agg:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table 'Worktable'. Scan count 3, logical reads 2860396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'temp'. Scan count 1, logical reads 1977, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3214 ms, elapsed time = 4017 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Old School Group By:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 3 ms.

Table 'temp'. Scan count 10, logical reads 3954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 809 ms, elapsed time = 364 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Rank:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table 'temp'. Scan count 5, logical reads 1977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3619 ms, elapsed time = 1840 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

SELECT MAX SUBQUERY:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table 'temp'. Scan count 10, logical reads 3954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 827 ms, elapsed time = 334 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

Some people beat me to it but this was my test:

SET NOCOUNT ON;

CREATE TABLE #temp (ID int identity primary key, [Date] DATE NOT NULL);

CREATE INDEX dt ON #temp([date]);

INSERT INTO #temp ([date])

SELECT TOP (100000) d

FROM (VALUES

('1/1/1900')

,('1/1/1900')

,(getdate() )

,(getdate() )) x(d), sys.all_columns a, sys.all_columns b

SET STATISTICS IO ON;

PRINT 'Win Agg:'

SELECT

T1.ID

,T1.Date

,CASE

WHEN MAX(T1.Date) OVER() = T1.Date THEN 1

ELSE 0

END AS MAX_DATE_FLAG

FROM #temp T1;

PRINT 'Old School Group By:';

SELECT

T1.ID

,T1.Date

,CASE WHEN t1.Date = x.md THEN 1 ELSE 0 END

FROM #temp T1

CROSS APPLY

(

SELECT MAX(t1.Date) FROM #temp t1

) x(md);

PRINT 'Rank:';

WITH ranked AS

(

SELECT RNK=RANK() OVER (ORDER BY [Date] DESC),

*

FROM #temp

)

SELECT ID,

[Date],

CASE WHEN RNK=1 THEN 1 ELSE 0 END

FROM ranked

ORDER BY ID;

SET STATISTICS IO OFF;

DROP TABLE #temp;

Results:

Win Agg:

Table 'Worktable'. [/u]Scan count 3, logical reads 202076, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#temp_______________________________________________________________________________________________________________000000000008'. Scan count 1, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Old School Group By:

Table '#temp_______________________________________________________________________________________________________________000000000008'. Scan count 2, logical reads 164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Rank:

Table '#temp_______________________________________________________________________________________________________________000000000008'. Scan count 1, logical reads 163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In a hurry but...

Worktable = bad (usually). Win Aggregate (without window spec) = lazy spool = worktable = lots more reads = bad!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 11

thanks a lot for your replies :-).

I think for my condition, updating the field worked.

I declared a variable, and set the variable to (select max(date) from table) and then used this variable in my case statements.

Thanks again

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 12

To continue the crusade for fairness, now that we're looking at time and not just IO, I'd probably want to remove the ORDER BY Id that I threw in to make the presentation nicer.

On that large data set it accounts for almost half the execution time.

One other fun point is that a clustered index on date helps all the other solutions dramatically, but not so much for the windowed aggregate.

First, results on my machine after removing the costly and unnecessary ORDER BY ID (also with MAXDOP 1, just to keep the picture simpler for now):

--------------------------------------------------------------------------------

Win Agg:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'Worktable'. Scan count 3, logical reads 2860396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'temp'. Scan count 1, logical reads 3718, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 7234 ms, elapsed time = 7261 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Old School Group By:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 3 ms, elapsed time = 3 ms.

Table 'temp'. Scan count 2, logical reads 7436, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1125 ms, elapsed time = 1580 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Rank:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'temp'. Scan count 1, logical reads 3718, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3969 ms, elapsed time = 4438 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

SELECT MAX SUBQUERY:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 3 ms.

Table 'temp'. Scan count 2, logical reads 7436, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1031 ms, elapsed time = 1033 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

And now with a clustered index on date:

--------------------------------------------------------------------------------

Win Agg:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'Worktable'. Scan count 3, logical reads 2860396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'temp'. Scan count 1, logical reads 4725, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 6828 ms, elapsed time = 6826 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Old School Group By:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 609 ms, elapsed time = 630 ms.

Table 'temp'. Scan count 2, logical reads 4728, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 563 ms, elapsed time = 551 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

Rank:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table 'temp'. Scan count 1, logical reads 4725, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 718 ms, elapsed time = 724 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------

SELECT MAX SUBQUERY:

--------------------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 4 ms.

Table 'temp'. Scan count 2, logical reads 4728, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 547 ms, elapsed time = 535 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Cheers!

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 13

Luis Cazares (4/20/2016)
Just to add timing to the performance tests.
CREATE TABLE temp (ID int, [Date] DATE);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4, E2
)
INSERT INTO temp
SELECT n, CAST( CAST( (RAND(CHECKSUM(NEWID())) * 2000) AS int) AS datetime) + '2010'
FROM cteTally
DECLARE @ID int, @Date datetime, @bit bit;
PRINT '--------------------------------------------------------------------------------';
PRINT 'Win Agg:'
PRINT '--------------------------------------------------------------------------------';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT
@ID = T1.ID
,@Date = T1.Date
,@bit = CASE
WHEN MAX(T1.Date) OVER() = T1.Date THEN 1
ELSE 0
END
FROM temp T1;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
PRINT '--------------------------------------------------------------------------------';
PRINT 'Old School Group By:';
PRINT '--------------------------------------------------------------------------------';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT
@ID = T1.ID
,@Date = T1.Date
,@bit = CASE WHEN t1.Date = x.md THEN 1 ELSE 0 END
FROM temp T1
CROSS APPLY
(
SELECT MAX(t1.Date) FROM temp t1
) x(md);
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
PRINT '--------------------------------------------------------------------------------';
PRINT 'Rank:';
PRINT '--------------------------------------------------------------------------------';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH ranked AS
(
SELECT RNK=RANK() OVER (ORDER BY [Date] DESC),
*
FROM temp
)
SELECT @ID = ID,
@Date = [Date],
@bit = CASE WHEN RNK=1 THEN 1 ELSE 0 END
FROM ranked
ORDER BY ID;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
DROP TABLE temp;
(1000000 row(s) affected)
--------------------------------------------------------------------------------
Win Agg:
--------------------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 102 ms.
Table 'Worktable'. Scan count 3, logical reads 2860396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'temp'. Scan count 1, logical reads 1977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3495 ms, elapsed time = 3758 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
--------------------------------------------------------------------------------
Old School Group By:
--------------------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 6 ms, elapsed time = 6 ms.
Table 'temp'. Scan count 10, logical reads 3954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1452 ms, elapsed time = 581 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
--------------------------------------------------------------------------------
Rank:
--------------------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'temp'. Scan count 5, logical reads 1977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6363 ms, elapsed time = 3908 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Be careful here, somewhat comparing apples and oranges as some tests have an order by clause and others not.

😎

USE TEEST;

GO

-- /* -- UNCOMMENT THIS LINE FOR REUSING THE TEST SET

IF OBJECT_ID(N'dbo.temp') IS NOT NULL DROP TABLE dbo.temp;

CREATE TABLE dbo.temp (ID int, [Date] DATE);

;WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

cteTally(n) AS(

SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E E1,E E2,E E3,E E4,E E5,E E6,E E7,E E8

)

INSERT INTO dbo.temp

SELECT n, CAST( CAST( (RAND(CHECKSUM(NEWID())) * 2000) AS int) AS datetime) + '2010'

FROM cteTally;

CREATE NONCLUSTERED INDEX NCLIDX_DBO_TEMP_DATE_INCL_ID ON dbo.temp (Date ASC) INCLUDE (ID);

CREATE NONCLUSTERED INDEX NCLIDX_DBO_TEMP_ID_INCL_DATE ON dbo.temp (ID ASC) INCLUDE (Date);

-- */

DECLARE @ID int, @Date datetime, @bit bit;

DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));

INSERT INTO @timer(T_TXT) VALUES('DRY RUN 1');

SELECT

@ID = T1.ID

,@Date = T1.Date

FROM dbo.temp T1;

INSERT INTO @timer(T_TXT) VALUES('DRY RUN 1');

INSERT INTO @timer(T_TXT) VALUES('DRY RUN 2');

SELECT

@ID = T1.ID

,@Date = T1.Date

FROM dbo.temp T1

WHERE T1.ID > 0

ORDER BY T1.ID;

INSERT INTO @timer(T_TXT) VALUES('DRY RUN 2');

INSERT INTO @timer(T_TXT) VALUES('DRY RUN 3');

SELECT

@ID = T1.ID

,@Date = T1.Date

FROM dbo.temp T1

WHERE T1.Date > '1900/01/01'

ORDER BY T1.Date;

INSERT INTO @timer(T_TXT) VALUES('DRY RUN 3');

PRINT '--------------------------------------------------------------------------------';

PRINT 'Win Agg:'

PRINT '--------------------------------------------------------------------------------';

INSERT INTO @timer(T_TXT) VALUES('Win Agg');

SELECT

@ID = T1.ID

,@Date = T1.Date

,@bit = CASE

WHEN MAX(T1.Date) OVER(PARTITION BY (SELECT NULL)) = T1.Date THEN 1

ELSE 0

END

FROM dbo.temp T1

--ORDER BY T1.ID;

INSERT INTO @timer(T_TXT) VALUES('Win Agg');

PRINT '--------------------------------------------------------------------------------';

PRINT 'Old School Group By:';

PRINT '--------------------------------------------------------------------------------';

INSERT INTO @timer(T_TXT) VALUES('Old School Group By');

SELECT

@ID = T1.ID

,@Date = T1.Date

,@bit = CASE WHEN t1.Date = x.md THEN 1 ELSE 0 END

FROM dbo.temp T1

CROSS APPLY

(

SELECT MAX(t1.Date) FROM temp t1

) x(md);

INSERT INTO @timer(T_TXT) VALUES('Old School Group By');

PRINT '--------------------------------------------------------------------------------';

PRINT 'Rank:';

PRINT '--------------------------------------------------------------------------------';

INSERT INTO @timer(T_TXT) VALUES('Rank');

WITH ranked AS

(

SELECT RNK=RANK() OVER (ORDER BY [Date] DESC)

,T.ID

,T.Date

FROM dbo.temp T

)

SELECT @ID = ID,

@Date = [Date],

@bit = CASE WHEN RNK=1 THEN 1 ELSE 0 END

FROM ranked;

--ORDER BY ID;

INSERT INTO @timer(T_TXT) VALUES('Rank');

PRINT '--------------------------------------------------------------------------------';

PRINT 'RankX:';

PRINT '--------------------------------------------------------------------------------';

INSERT INTO @timer(T_TXT) VALUES('RankX');

SELECT

@ID = T1.ID

,@Date = T1.Date

,@bit = CASE

WHEN DENSE_RANK() OVER

(

ORDER BY T1.Date DESC

) = 1 THEN 1

ELSE 0

END --AS MAX_DATE_FLAG

FROM dbo.temp T1;

INSERT INTO @timer(T_TXT) VALUES('RankX');

SELECT

T.T_TXT

,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

FROM @timer T

GROUP BY T.T_TXT

ORDER BY DURATION;

Results on a 2nd gen i5 laptop, SQL Server 2014

T_TXT DURATION

--------------------- -----------

DRY RUN 180000

Old School Group By 300001

Rank 350000

RankX 350001

Win Agg 2667005

Results on a 3rd gen i7, SQL Server 2016(RC3)

T_TXT DURATION

--------------------- -----------

DRY RUN 3 153652

DRY RUN 1 162537

DRY RUN 2 200521

Old School Group By 231823

RankX 263028

Rank 269540

Win Agg 2488899

drew.allen SSC Guru Points: 76997 More actions · Answer 14

Is there some reason that you chose to use CROSS APPLY rather than CROSS JOIN? I tend to only use CROSS APPLY if the right side is somehow dependent on the left side, which is not the case in this example.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA