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 2012 Forums
 Transact-SQL (2012)
 How to order

Author  Topic 

Rauken
Posting Yak Master

108 Posts

Posted - 2013-08-13 : 07:59:09
Hi,

I have a table called DeliveryPosition. To simplify my question I've stripped a number of fields.

Table and data looks like this:

DeliveryPosId Desc PrecisionId
22591 Main1 NULL
22592 Main2 NULL
22872 test1 22591
22873 test2 22591
22874 test11 22592
22875 test22 22592

I want to have DeliveryPosId = 22591 and then the rows with PrecisionId = 22591. Then main row no 2 which is DeliveryPosId = 22592 and the corresponding rows.

I'm stuck! Is there an easy way to do this in t-sql or do I need view, temporary table etc?

/Magnus

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-13 : 08:19:07
SELECT * FROM dbo.Table1
ORDER BY COALESCE(PrecisionID, DeliveryPosID), DeliveryPosID;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Rauken
Posting Yak Master

108 Posts

Posted - 2013-08-13 : 08:26:07
WOW! Thanks it worked. I forgot about using COALESCE.
Go to Top of Page

Rauken
Posting Yak Master

108 Posts

Posted - 2013-08-16 : 07:10:45
Hi again, I found a new problem. Sometimes PrecisionId can be 0 instead of NULL then using COALSCE won't work. Any ideas?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 07:12:59
ORDER BY COALESCE(NULLIF(PrecisionID,0), DeliveryPosID), DeliveryPosID;


--
Chandu
Go to Top of Page

Rauken
Posting Yak Master

108 Posts

Posted - 2013-08-16 : 07:31:28
Thanks! It works perfect.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 07:39:57
quote:
Originally posted by Rauken

Thanks! It works perfect.


Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -