Order by desc for specific date

Question

Order by desc for specific date

mynkdby

SSC Eights!

Points: 846
More actions
May 27, 2014 at 11:38 am

#308560

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

mynkdby SSC Eights! Points: 846 More actions · Answer 3

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

mynkdby SSC Eights! Points: 846 More actions · Answer 5

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..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

mynkdby SSC Eights! Points: 846 More actions · Answer 7

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