Piling on

😎

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

CREATE TABLE dbo.TBL_SAMPLE_ACTIVITY_DATA

(

Workid INT IDENTITY(1,1) NOT NULL

,Workname NVARCHAR(100) NOT NULL

,Activitycode NVARCHAR(100) NULL

);

INSERT INTO dbo.TBL_SAMPLE_ACTIVITY_DATA (Workname,Activitycode)

VALUES

(N'Drilling' ,N'S16-03779-01/016' )

,(N'planting' ,N'S16-03779-01/011' )

,(N'Field Work',NULL )

,(N'Lathe work',NULL )

,(N'planting' ,N'S16-03778-01/011' )

,(N'planting' ,N'S16-03777-01/011' )

;

SELECT

SAD.Workid

,SAD.Workname

,SAD.Activitycode

FROM dbo.TBL_SAMPLE_ACTIVITY_DATA SAD

ORDER BY ISNULL(SAD.Activitycode,N'ZZZZZZZZZZZZZZZZZZZZ')

,SAD.Workname;

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER

(

ORDER BY SAD.Activitycode

,SAD.Workname

) + CASE

WHEN SAD.Activitycode IS NOT NULL THEN 0

ELSE 2000000000

END AS SORT_RID

,SAD.Workid

,SAD.Workname

,SAD.Activitycode

FROM dbo.TBL_SAMPLE_ACTIVITY_DATA SAD

)

SELECT

BD.Workid

,BD.Workname

,BD.Activitycode

FROM BASE_DATA BD

ORDER BY BD.SORT_RID ASC;