T-SQL Order by on date descending in a column

Question

Post reply

T-SQL Order by on date descending in a column

etirem

SSCommitted

Points: 1655
More actions
June 18, 2014 at 12:29 pm

#294710

Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),
(111,'2012-02-14 00:00:00.000','C is alphabet'),
(444,'2013-03-14 00:00:00.000','A is alphabet'),
(111,'2012-04-14 00:00:00.000','C is alphabet'),
(222,'2012-08-15 00:00:00.000','C is alphabet'),
(333,'2014-09-20 00:00:00.000','C is alphabet'),
(444,'2012-10-26 00:00:00.000','B is alphabet'),
(222, '2013-01-14 00:00:00.000','A is alphabet'),
(111,'2013-02-14 00:00:00.000','C is alphabet'),
(222,'2013-03-14 00:00:00.000','B is alphabet'),
(444,'2014-04-14 00:00:00.000','C is alphabet'),
(333,'2012-05-14 00:00:00.000','C is alphabet')
--SELECT * FROM #tTable
--ORDER BY 1, 2 DESC
SELECT u.CID,
STUFF(( SELECT '; ' +CAST(tu.Dept AS VARCHAR(MAX))+' '+CONVERT(VARCHAR(25), tu.CDate,101)
FROM #tTable tu
WHERE tu.CID = u.CID
ORDER BY RIGHT('; ' +CAST(tu.Dept AS VARCHAR(MAX))+' '+CONVERT(VARCHAR(25), tu.CDate,101),10)
FOR XML PATH('')),1,1,'') AS DepartmentandDate
FROM #tTable u
GROUP BY u.CID
DROP TABLE #tTable

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

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

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

You were overcomplicating your ORDER BY clause.

SELECT u.CID,

STUFF(( SELECT '; ' +CAST(tu.Dept AS VARCHAR(MAX))+' '+CONVERT(VARCHAR(25), tu.CDate,101)

FROM #tTable tu

WHERE tu.CID = u.CID

ORDER BY tu.CDate DESC

FOR XML PATH('')),1,1,'') AS DepartmentandDate

FROM #tTable u

GROUP BY u.CID

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Thank you for easily consumable ddl and sample data!!!

Not sure what that crazy order is in your query. Just order by the columns you want ordered.

SELECT u.CID,

STUFF(( SELECT '; ' +CAST(tu.Dept AS VARCHAR(MAX))+' '+CONVERT(VARCHAR(25), tu.CDate,101)

FROM #tTable tu

WHERE tu.CID = u.CID

--ORDER BY RIGHT('; ' +CAST(tu.Dept AS VARCHAR(MAX))+' '+CONVERT(VARCHAR(25), tu.CDate,101),10)

order by CID, CDate desc

FOR XML PATH('')),1,1,'') AS DepartmentandDate

FROM #tTable u

GROUP BY u.CID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Seems that once again Luis "Sir Speedy" beat me to the punchline again. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

etirem SSCommitted Points: 1655 More actions · Answer 4

Thank you!.. I only had order by cdate desc and not "order by CID, CDate desc".

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

You don't need the order by CID as there's only one CID on your subquery.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Luis Cazares (6/18/2014)
You don't need the order by CID as there's only one CID on your subquery.

True that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/