Sort by Date in SQL

13,968
ORDER BY
  (MONTH(resource_date) - MONTH(GETDATE()) + 12) % 12,
  DATEADD(year, YEAR(GETDATE()) - YEAR(resource_date), resource_date),
  YEAR(resource_date)

The first term sets the primary order by the month of resource_date (the current month will be first, the previous one, last). The second term orders the timestamps within a month regardless of the year of the date. If your dates do not contain time parts or if the time parts are absolutely irrelevant, you could replace it with DAY(resource_date). Finally, the last term takes the year into account for otherwise identical dates (could also be simply resource_date).

Share:
13,968

Related videos on Youtube

Brett
Author by

Brett

Updated on June 04, 2022

Comments

  • Brett
    Brett over 1 year

    I have a resources table, one of the fields is a date field with the Data Type of date. I want to have to following output:

    Current month records (say May - year is not important)

    Then the following (again, assuming May is the current month)

    • June Records
    • July Records
    • August Records
    • September Records
    • October Records
    • November Records
    • December Records
    • January Records
    • February Records
    • March Records
    • April Records

    Come June, June is the current month and then the order would be:

    • July Records
    • August Records
    • ...

    Here is my SQL...I don't know how to ORDER the output to achieve the desired order (5,6,7,8,9,10,11,12,1,2,3,4):

    SELECT
      resource_id,
      resource_title,
      resource_summary,
      resource_category,
      resource_status,
      resource_date,
      DATEPART(month, resource_date) AS resource_month,
      DATEPART(day, resource_date) AS resource_day
    FROM dbo.resources
    WHERE (resource_category = N'Quotes')
      AND (resource_status <> N'Draft')
    

    I found this possible solution for MySQL:

    I need unusual ordering mysql results

    but I'm missing something on my end.

  • Brett
    Brett over 12 years
    Okay, this is the first suggestion I tried. In MS SQL, it is listing the records correctly. When I output the results to a web page, the order is not correct. Here is a sample: americanspeaker.com/beta/members/sample.asp
  • Brett
    Brett over 12 years
    Here is the SQL in MS SQL SELECT TOP (100) PERCENT resource_id, resource_title, resource_summary, resource_source, resource_date, resource_category, resource_status FROM dbo.resources WHERE (resource_category = N'Quotes') AND (NOT (resource_status = N'Draft')) ORDER BY (MONTH(resource_date) - MONTH(GETDATE()) + 12) % 12, DAY(resource_date)
  • Brett
    Brett over 12 years
    Here is the code that pulls from the DB Dim rs_quotes Dim rs_quotes_cmd Dim rs_quotes_numRows Set rs_quotes_cmd = Server.CreateObject ("ADODB.Command") rs_quotes_cmd.ActiveConnection = MM_americanspeaker_STRING rs_quotes_cmd.CommandText = "SELECT * FROM dbo.qry_resources_quotes" rs_quotes_cmd.Prepared = true Set rs_quotes = rs_quotes_cmd.Execute rs_quotes_numRows = 0
  • Andriy M
    Andriy M over 12 years
    The ORDER BY should be applied to the final query, if you want guaranteed order. You must not rely on the ORDER BY clause in the definition of dbo.qry_resources_quotes (which, I assume, is defined by the previous query, the SELECT TOP (100) PERCENT... one).
  • Brett
    Brett over 12 years
    Works! Awesome. I've run into that ORDER BY issue in the past. Is this a bug in MS SQL or something I'm not understanding about SQL and MS SQL Server?
  • Andriy M
    Andriy M over 12 years
    From the MSDN article about CREATE VIEW: Note. The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.