Custom Order by in SQL Server Like P, A, L, H

SQL Custom Sort By

select letter
from letters
order by
   case letter
     when 'a' then 0
     when 'b' then 1
     when 'd' then 2
     when 'e' then 3
     when 'c' then 4
   end

Custom sorting by month name in SQL Server

Of all the answers and suggestions I have come across here, I find this way (suggested by xQbert in the question's comments) to be the simplest one :

SELECT      DateName(MONTH, e.DateOfEntry) + ' ' + CONVERT(NVARCHAR(100), YEAR(e.DateOfEntry)) AS MonthOfEntry,
            MONTH(e.DateOfEntry) AS MonthNumber,
            SUM(e.Entries) AS TotalEntries
FROM        Entry e
WHERE       e.DateOfEntry BETWEEN @StartDate AND (DATEADD(YEAR, 1, @StartDate))
GROUP BY    MONTH(e.DateOfEntry), DateName(MONTH,e.DateOfEntry), YEAR(e.DateOfEntry)
ORDER BY    YEAR(e.DateOfEntry) ASC, MONTH(e.DateOfEntry) ASC

A fiddle to demonstrate this : http://rextester.com/CJFFP5640

Initially, I was using the following query :

SELECT      sortingList.MonthOfEntry,
            sortingList.TotalEntries,
            sortingList.MonthNumber
FROM        (
              SELECT            DateName(MONTH, DATEADD(MONTH, MONTH(e.DateOfEntry), 0) - 1) + ' ' + CONVERT(nvarchar(20),YEAR(e.DateOfEntry)) AS MonthOfEntry,
                                SUM(e.Entries) as TotalEntries,
                                CASE
                                    WHEN    ((MONTH(e.DateOfEntry) - MONTH(@StartDate)) > 0)
                                    THEN    (MONTH(e.DateOfEntry) - MONTH(@StartDate)) + 1
                                    WHEN    ((MONTH(e.DateOfEntry) - MONTH(@StartDate)) = 0)
                                    THEN    1
                                    ELSE 
                                            ((12 - MONTH(@StartDate)) + (MONTH(e.DateOfEntry))) + 1
                                END
                                AS MonthNumber
              FROM              Entry e
              WHERE             e.DateOfEntry >= @StartDate AND e.DateOfEntry < DATEADD(YEAR, 1, @StartDate)
              GROUP BY          DateName(MONTH, DATEADD(MONTH, MONTH(e.DateOfEntry), 0) - 1), YEAR(e.DateOfEntry), MONTH(e.DateOfEntry) - MONTH(@StartDate), MONTH(e.DateOfEntry)
            ) sortingList
ORDER BY    sortingList.MonthNumber ASC

Here's an Fiddle to demonstrate this : http://rextester.com/LEVD30653

Explanation (non TL;DR)

You can see that it's essentially the same WHERE clause. However, the query at the top uses much simpler logic for sorting and is more fluent and readable.

Do note that the second solution (using the CASE statement) sorts the month numbers as per the user-provided month number i.e. if the user provides December 2015, then the second solution will number Dec 2015 as 1, January 2016 as 2, February 2016 as 3 and so on and so forth. This might be more beneficial in cases where you want to work on top of this data.

As far as my use-case is concerned, this makes more sense. However, as far as the scope of the question is concerned, the query at the top is the best one.

MySQL ORDER BY [custom SET field value]

Thought I'd add another way to order by custom field values,

ORDER BY FIND_IN_SET(status, 'available,busy,distance,offline')

(If the given strings contain a quote simply escape it)

Return matching SQL results in defined order?

Using what I found here and here.

select *
from yourTable
order by case when Codes = "A" then 1
              when Codes = "C" then 2
              when Codes = "X" then 3
              when Codes = "D" then 4
              when Codes = "F" then 5
              else 6
         end asc,
         IF(Names RLIKE '^[a-z]', 1, 2), 
         Names

SSRS custom sort one column ASC and DESC

In the serial number group you will set two sorting expressions:

An ascending on

=Iif(Fields!Sales_Status.Value="Past Sales", Fields!Serial_Number.Value,"")

A descending on

=Iif(Fields!Sales_Status.Value="Future Sales", Fields!Serial_Number.Value,"")

How to retrieve rows that begin and end with specific characters?

I think you are looking for something like:

SELECT ID, NAME FROM STUDENT WHERE NAME LIKE 'ab%k';

For wildcard operations you should use a WHERE clause with the LIKE keyword that allows you to filter columns that match a given pattern using the %symbol as a wildcard.

There is a question about the List of special characters for SQL LIKE clause that has a good list of LIKE special characters

---

Related Topics