Retrieving Max Values on Table with Null Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving Max Values on Table with Null Values

Author

Topic

windy
Starting Member

4 Posts

Posted - 2009-04-14 : 14:52:38

I am trying to confirm the correct technique for finding the latest records in a table with null values. Here is a simplified example:

CREATE TABLE [dbo].[test](
[id] [int] NOT NULL,
[colordate] [datetime] NOT NULL,
[color] [nvarchar](10) NOT NULL,
[shade] [nvarchar](10) NULL,
[score] [int] NULL
) ON [PRIMARY]

INSERT test VALUES (1,'Apr 1, 2009',N'white',NULL,1)
INSERT test VALUES (2,'Apr 2, 2009',N'red',N'dark',3)
INSERT test VALUES (3,'Apr 3, 2009',N'red',N'light',1)
INSERT test VALUES (4,'Apr 4, 2009',N'white',NULL,5)
INSERT test VALUES (5,'Apr 1, 2009',N'red',N'light',2)
INSERT test VALUES (6,'Apr 2, 2009',N'white',NULL,2)
INSERT test VALUES (7,'Apr 3, 2009',N'red',N'dark',4)
INSERT test VALUES (8,'Apr 4, 2009',N'blue',NULL,3)
INSERT test VALUES (9,'Apr 1, 2009',N'blue',N'light',4)
INSERT test VALUES (10,'Apr 2, 2009',N'blue',N'light',2)

select t1.* from test t1
inner join (select color, shade, max(colordate) as colordate from test group by color, shade) t2
on t1.color = t2.color and (t1.shade = t2.shade or (t1.shade is null and t2.shade is null)) and t1.colordate = t2.colordate

Note how I created the join on the nullable shade column.

Is this the right technique and the best way to join the two sets?

Thanks,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-14 : 16:01:30

[code]Select * from
(Select id,colordate,color,shade,score,ROW_NUMBER() over (Partition by color, shade Order by colordate desc) as ROWID
from test) Z
Where Z.ROWID =1[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 16:06:03

here are a couple alternatives (one is basically the same as sodeep):


select * 
from   test t
where  colordate = (
       select max(colordate)
       from   test
       where  color = t.color
       and    isNull(shade,'null') = isNull(t.shade, 'null')
       )      

select [id]
       ,[colordate]
       ,[color]
       ,[shade]
       ,[score]
from   (
       select [id]
              ,[colordate]
              ,[color]
              ,[shade]
              ,[score]
              ,row_number() over (partition by [color], [shade] order by colordate desc) as rn
       from   test 
       ) d
where  rn = 1

Be One with the Optimizer
TG

windy
Starting Member

4 Posts

Posted - 2009-04-14 : 16:15:29

Thanks sodeep and TG - my bad : I posted in the wrong forum as I need soln to be SQL 2000 compatible.

Can I move it down to the SQL 2000 forums?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 16:26:22

naw that's ok - unless you still need help. my first example will work with 2000. There are other ways as well if you want more.

Be One with the Optimizer
TG

windy
Starting Member

4 Posts

Posted - 2009-04-14 : 16:31:15

http://blogs.mssqltips.com/forums/t/509.aspx

TG - at the above link a poster suggests that substituting a string value may not be that efficient. What do you say?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 16:36:14

Best thing to do is to look at query plans and execution times to see for yourself. Here is another way. It's a little ugly but should be quite efficient:


select convert(int, substring(s, 23+1, 12))      as [id]
       ,convert(datetime, substring(s, 1, 23))   as colorDate
       ,color
       ,shade
       ,convert(int, substring(s, 23+12+1, 12))  as score
from   (
       select color
              ,shade
              ,max(convert(char(23), colordate, 121)
                + str([id],12)
                + str(score, 12)) as s
       from   test 
       group by color, shade
       ) d

Be One with the Optimizer
TG

windy
Starting Member

4 Posts

Posted - 2009-04-14 : 16:45:34

Thanks again TG - is there anything wrong with my original query since I understand the logic more clearly?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 17:02:50

well all the version posted return the same results so I guess it's a matter of efficiency. As I suggested earlier, you should compare the execution plans and io stats and execution times on your actual data to see which performs the best.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources