Order by desc for specific date

  • There is a table "Marketshare" which has fields say "startdate" and "rate". i need to sort the records of this table and have to take the top 1 latest record on 1 july of each year.

    Was trying with Order by along with datepart() didn't able understand how to proceed.

    MarketShare<table>

    StartDate || Rate

    -------------------

    22/06/2014 234

    28/06/2014 789

    29/06/2014 987

    02/07/2014 890

    I need the first record which has start date before 1 july.

    Result :

    StartDate || Rate

    ---------------------

    29/06/2014 987

  • Please post your query with sample data and we can assist.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • something like this help a little?

    ORDER BY

    CASE

    WHEN datepart(mm,startdate) = 7

    AND datepart(dd,startdate) = 1

    THEN 1

    ELSE 99

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi ,

    MarketShare<table>

    StartDate || Rate

    -------------------

    22/06/2014 234

    28/06/2014 789

    29/06/2014 987

    02/07/2014 890

    i need latest start date record on july 1

    Result :

    StartDate || Rate

    ---------------------

    29/06/2014 987

    thanks in advance

  • Something like this?

    DECLARE @date = '20140701'

    SELECT TOP 1 *

    FROM MarketShare

    WHERE StartDate <= @date

    ORDER BY StartDate DESC

    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
  • Actually i am using this as a sub query inside my view, Declaring a date won't work because it should work for coming year records too..

  • Here's a quick and dirty script that seems to get what you want.

    SET DATEFORMAT DMY

    DECLARE @MarketShare TABLE (startdate DATE, rate INT)

    INSERT INTO @MarketShare

    ( startdate, rate )

    VALUES (

    '22/06/2014', 234),(

    '28/06/2014', 789),(

    '29/06/2014', 987),(

    '02/07/2014', 890)

    SELECT TOP 1 *

    FROM @MarketShare

    WHERE startdate <= '1/7/2014'

    ORDER BY startdate DESC

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I achieved it proceeding like this

    select startdate, case when startdate>=convert(datetime,'1 July '+convert(varchar,datepart(yyyy,GETDATE())))

    then '1 Jan 1900' else startdate end Datecalc from MARKETSHARE order by Datecalc desc

    anyway thanks all of you for providing me the idea to proceed

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply