problem ordering result - SQL Server Forums

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

problem ordering result

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2009-02-11 : 09:11:29

I have a table which has 5 columns. of these columns i need to order by EmployeeID and MadeByID

problem is the madebyid will be the same for the entire result set and the employeeid will be different ids but contain the madebyid

how can i get whatever the madebyid is to be the top result?

sample data

employeeid 19 madebyid 7
employeeid 7 madebyid 7

how can i write a query that will make this data order like this

employeeid 7 madebyid 7
employeeid 19 madebyid 7

you cant do a simple sort by because the madebyid could be any number but it will always be one of the employeeids

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:12:50

isnt this enough (atleast as per posted sample)?

ORDER BY employeeid,madebyid

tpiazza55
Posting Yak Master

162 Posts

Posted - 2009-02-11 : 09:16:43

that will not work for the situation

when i have

employeeid 7 madebyid 19
employeeid 19 madebyid 19

and need it to output

employeeid 19 madebyid 19
employeeid 7 madebyid 19

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:23:51

oh ok..i think i got you. then what you want is this


SELECT other fields..., employeeid,madebyid
FROM
(
SELECT other fields..., employeeid,madebyid ,case when employeeid=madebyid then 0 else 1 end as ord
FROM Table
)t
ORDER BY ord,employeeid,madebyid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 09:29:35

SELECT *
FROM Employees
ORDER BY NULLIF(EmployeeID, MadeByID), EmployeeID

E 12°55'05.63"
N 56°04'39.26"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2009-02-11 : 09:30:51

think that got it thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:32:52