ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Order By with Sub Query in SQL ServerHi @paulrajmca,<br />Please try the following<br /><br /><a href = 'link(Threads/244859/Show-Last-7-Records-using-Order-By-with-Sub-Query-in-SQL-Server/)'>Show Last 7 Records using Order By with Sub Query in SQL Server</a><br /><br />It might help you.<br /><br />Cheers Andrea., 03 Oct 2018 07:19:56 GMTOrder By with Sub Query in SQL Server<ul> <li>thanking you pandeyism for your helpful reply and interest .then thanks to all&nbsp;</li> <li>i got the answer&nbsp;</li> </ul> <p>&nbsp;this is the line i have changed&nbsp;</p> <pre class="brush: sql">SELECT q1.*, (case when childid = @childid then 0 else 1 end) as ClickOrder, </pre> <pre class="brush: sql">SELECT q1.*, (case when childid = @childid then 0 else 1 end) as ClickOrder, CASE WHEN MONTH(q1.Birthdate)=MONTH(GETDATE()) AND DAY(q1.Birthdate)&gt;=DAY(GETDATE()) THEN 0 WHEN MONTH(q1.Birthdate)&gt;MONTH(GETDATE()) THEN 1 ELSE 2 END AS ChildOrder FROM ( SELECT PD.PledgeId, CD.ChildID, CD.GivenName as ChildName, CD.Birthdate, (CONVERT(int,DATEDIFF(d, CD.Birthdate, GETDATE()) / 365.25)+1) AS Age, DATENAME(MONTH, CD.Birthdate) AS BirthMonth, CONVERT(CHAR(3), DATENAME(MONTH, CD.Birthdate)) AS BirthMonth2, DAY(CD.Birthdate) as BirthDay, 2 AS NoDays, CD.WebPhotoPath as WebPhotoPath, PD.PaidThruDate, CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) as BtnBDayDate1, CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) as BtnBDayDate2 --ROW_NUMBER()OVER (PARTITION BY cd.childid ORDER By cd.birthdate asc) as RowNumbers FROM Pledge_PledgeDetails PD INNER JOIN Child_ChildDetails CD ON CD.ChildID=PD.ChildID WHERE PD.LeadID=@LeadID AND CD.ProjectID not in (167948,197358,178124) AND PD.Status in (2) and (CONVERT(DATE,GETDATE()) -- Display child&#39;s birthday with 45 days interval BETWEEN CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) AND CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) OR CONVERT(DATE,GETDATE()) BETWEEN CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), (YEAR(GETDATE())-1)))) AND CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), (YEAR(GETDATE())-1)))) ) ) AS q1 WHERE q1.ChildID NOT IN (SELECT ChildID FROM CMS_ChildBirthdayWishes WHERE CreatedDate BETWEEN q1.BtnBDayDate1 AND q1.BtnBDayDate2) ORDER BY ClickOrder, ChildOrder, MONTH(q1.Birthdate), DAY(q1.Birthdate) ASC</pre> <p>&nbsp;</p>, 05 Oct 2018 05:57:42 GMT