Ordering by the order of values in a SQL IN clause

Question

I was curious if there's any way (possibly a better way) to order by the order of the values in an IN() clause.

The problem is if we have 2 queries, one that gets all of the IDs and the other retrieves all the information. The first creates the order of the IDs which we want the second to order by. The IDs are put in an IN() clause in the correct order.

Answer 1

We can use expressions as well in Order by clause. Suppose we want to sort for a year in a date column. Let us run the query without any Order by clause first.

SELECT top 10 [NationalIDNumber]

  , SickLeaveHours as [SickHours]

      ,[Vacationhours]

      ,[BirthDate]

      ,[MaritalStatus]

      ,[Gender]

      ,[ModifiedDate]

      ,[SalariedFlag]

FROM [AdventureWorks2017].[HumanResources].[Employee]

Where MaritalStatus='M'

Now, we want to sort on Birthdate column yearly in ascending order. I am using the TOP clause to limit result in the output. We are using DATEPART function we get a specified part of a date. In this query, we want to extract year to sort data using the DATEPART function.

1 2 3 4 5 6 7 8 9 10 11

SELECT top 10 [NationalIDNumber] , SickLeaveHours as [SickHours] ,[Vacationhours] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' Order by DATEPART(YEAR , BirthDate) ASC