Selecting Minimums with NULLS? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Selecting Minimums with NULLS?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-04-05 : 20:01:38

Hi,

Anyone have any ideas about how to tackle this query below?

For each unique ProductID below, I'd like to select the row with the initial Name setting.

create table #Products (ProductID int, ProdCat int, Name varchar(10), ProdNameChgID int,OldNameHist varchar(10), NewNameHist varchar(10))

insert into #Products values (1,100,'Buggy',NULL,NULL,NULL),
(1,100,'Car',1234,'Buggy','Car'),
(1,100,'Plane',5678,'Car','Plane'),
(2,200,'Bike',NULL,NULL,NULL),
(3,300,'Skates',NULL,NULL,NULL),
(4,400,'Moped',NULL,NULL,NULL),
(5,100,'Plates',7890,'Dishes','Plates'),
(5,100,'China',9876,'Plates','China'),
(6,200,'Knives',NULL,NULL,NULL),
(7,300,'Forks',NULL,NULL,NULL),
(8,400,'Spoons',NULL,NULL,NULL)

select * from #Products

My result should be:
1,100,'Buggy',NULL,NULL,NULL
2,200,'Bike',NULL,NULL,NULL
3,300,'Skates',NULL,NULL,NULL
4,400,'Moped',NULL,NULL,NULL
5,100,'Plates',7890,'Dishes','Plates'
6,200,'Knives',NULL,NULL,NULL)
7,300,'Forks',NULL,NULL,NULL)
8,400,'Spoons',NULL,NULL,NULL

Essentially I'd like to select each row with either the smallest ProdNameChgID number (NULL being the smallest value), Grouped by ProductID and ProdCat.

Thanks!

--PhB

singularity
Posting Yak Master

153 Posts

Posted - 2012-04-05 : 20:27:22

[code]
select ProductID, ProdCat, Name, ProdNameChgID, OldNameHist, NewNameHist
from
(select ProductID, ProdCat, Name, ProdNameChgID, OldNameHist, NewNameHist,
row_number() over (partition by ProductID, ProdCat order by isnull(ProdNameChgID,0)) as rn
from #Products
) a
where rn = 1
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 00:49:21

[code]
select ProductID, ProdCat, Name, t.ProdNameChgID, OldNameHist, NewNameHist
from table t
cross apply (select min(coalesce(ProdNameChgID,0)) as ProdNameChgID
from table
where ProductID = t.ProductID
and ProdCat = t.ProdCat
)t1
where t1.ProdNameChgID = coalesce(t.ProdNameChgID,0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-04-06 : 09:17:52

Thank You!

When I applied the singularity's suggestion to my real data it did not work, but with visakh16's suggestion everything just fell into place right away and worked nicely. I wanted singularity's to work though because it has less moving parts.

In any event, thanks to both!!

--PhB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 10:21:40

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources