How to sort sql result set like attached image

Question

How to sort sql result set like attached image

emadkhanqai

SSC Veteran

Points: 225
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: 246758 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

drew.allen SSC Guru Points: 76950 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: 183698 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: 76950 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