How to sort sql result set like attached image

Question

How to sort sql result set like attached image

emadkhanqai

SSC Veteran

Points: 275
More actions
March 8, 2016 at 8:03 am

#311968

Above is my sql query set which i want to sort like below
My original query is this (means it should be sorted first by project id , then inside that project id it should be sorted to ownstaffID :
SELECT * from (
SELECT dbo.MapSysGroupToUser.GrpID, dbo.MapSysGroupToUser.CategName, dbo.MapSysGroupToUser.SubCategName ,
dbo.d_project_p.proj_name AS ProjectName , MapGrpsToSysFunctions.ProjID AS PROJID , 0 AS OwnStaffID
FROM dbo.MapSysGroupToUser INNER JOIN
dbo.MapGrpsToSysFunctions ON dbo.MapGrpsToSysFunctions.Grp_Usr_ID = dbo.MapSysGroupToUser.GrpID INNER JOIN
dbo.d_project_p ON dbo.MapGrpsToSysFunctions.ProjID = dbo.d_project_p.proj_id
WHERE (dbo.MapSysGroupToUser.Is_Dell IS NULL) AND (dbo.MapSysGroupToUser.OwnStaffID IS NULL) AND
(dbo.MapGrpsToSysFunctions.Do_ID = 3) AND
(dbo.MapSysGroupToUser.OwnStaffID IS NULL)
UNION
SELECT dbo.MapSysGroupToUser.GrpID, 'OWN STAFF TEAM' AS CategName, dbo.d_office_title.Description as SubCategName, dbo.d_project_p.proj_name AS ProjectName ,
dbo.d_project_p.proj_id AS PROJID
,MapSysGroupToUser.OwnStaffID FROM dbo.MapSysGroupToUser INNER JOIN
dbo.d_office_title ON dbo.MapSysGroupToUser.OwnStaffID = dbo.d_office_title.ID INNER JOIN
dbo.MapGrpsToSysFunctions ON dbo.MapGrpsToSysFunctions.Grp_Usr_ID = dbo.MapSysGroupToUser.GrpID INNER JOIN
dbo.d_project_p ON dbo.MapGrpsToSysFunctions.ProjID = dbo.d_project_p.proj_id
WHERE (dbo.MapSysGroupToUser.Is_Dell IS NULL) AND (dbo.MapSysGroupToUser.OwnStaffID IS NOT NULL) AND (dbo.MapGrpsToSysFunctions.Do_ID = 3)
AND (dbo.MapSysGroupToUser.OwnStaffID IS NOT NULL)
) t

Viewing 6 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Please post table scripts, sample data and expected output. Screenshot are not good enough: we cannot copy and paste code from your screenshots.

See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

-- Gianluca Sartori

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Notwithstanding the above request, it looks like the column you are trying to sort on is an INT and you want to sort it as if it were a VARCHAR().

So CAST the column to a VARCHAR and ORDER BY that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76987 More actions · Answer 3

To me, it looks like you want

ORDER BY ProjID, OwnStaffID DESC

This will sort by ProjID and within ProjID it will sort by OwnStaffID descending.

Dre

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

Or maybe something like this:

ORDER BY ProjID, NULLIF(CategName, 'OWN STAFF TEAM')

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

drew.allen SSC Guru Points: 76987 More actions · Answer 5

I also considered the following in case he wanted zeroes last, but the rest in ascending order (assuming that OwnStaffID is never negative).

ORDER BY ProjID, SIGN(OwnStaffID) DESC, OwnStaffID

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA