StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?
primarykey
Id
3659981
data
AcceptedAnswerId
3660100
AnswerCount
3
ClosedDate
CommentCount
6
CommunityOwnedDate
CreationDate
2010-09-07T15:15:35.637
FavoriteCount
1
LastActivityDate
2013-08-21T16:43:10.773
LastEditDate
2010-09-07T16:10:10.970
LastEditorUserId
112194
OwnerUserId
112194
ParentId
0
PostTypeId
1
Score
3
ViewCount
3421
LastEditorDisplayName
text
Body
I have stored procedure that returns results sorted dynamically. The parent folder (this is for content management) has a RankTypeID field that allows sorting by Rank (0), Start Date in ascending order (1), Start Date in descending order (2), and document title (3) Rank is an integer, date is smalldatetime, and title is a nvarchar. ... <pre><code>ORDER BY Case Parent.RankTypeID When 0 Then dbo.Folders.Rank When 1 Then Cast(dbo.Documents.SortableDateStart As bigint) When 2 Then (1 - Cast(dbo.Documents.SortableDateStart As bigint)) When 3 Then Cast(dbo.Documents.Title as sql_variant) End </code></pre> I set up the SortableDateStart as a computed column to take a DateStart smalldatetime column and convert it into a bigit for sorting. It takes an ISO8601 date (designed for xml usage, and also handy for sorting) and replaces the T, :, and - <pre><code>(replace(replace(replace(CONVERT([varchar](16),[DateStart],(126)),'T',''),'-',''),':','')) </code></pre> This is kind of ugly. Is there a better way to do this? I'm also open to better ways of handling this dynamic sorting. Edit: Test Data Setup <pre><code>DECLARE @Temp TABLE ( [Rank] int, [Title] nvarchar(100), [DateStart] datetime ) INSERT into @Temp SELECT 1, 'title1', '1/1/2010 10:01:00AM' UNION SELECT 2, 'atitle1', '1/1/2010 10:03:00AM' UNION SELECT 3, 'title1', '1/1/2010 10:10:00AM' UNION SELECT 4, 'btitle1', '1/1/2010 10:04:00AM' UNION SELECT 10, 'title1', '1/1/2010 10:07:00AM' UNION SELECT 11, 'dtitle1', '1/1/2010 10:09:00AM' UNION SELECT 12, 'ctitle1', '1/1/2010 10:00:01AM' UNION SELECT 13, 'title1', '1/1/2010 10:10:00AM' DECLARE @RankTypeID tinyint --SET @RankTypeID = 0 -- rank --SET @RankTypeID = 1 -- date start asc SET @RankTypeID = 2 -- date start desc --SET @RankTypeID = 3 -- title SELECT [Rank], [DateStart], [Title] FROM @Temp ORDER BY Case @RankTypeID When 0 Then [Rank] When 1 Then Cast([DateStart] As sql_variant) When 3 Then [Title] else null End, Case @RankTypeID When 2 Then Cast([DateStart] As sql_variant) End DESC </code></pre>
Tags
<sql-server-2005><tsql>
Title
SQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USScottE
UserOwnerUserId
1. USScottE
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POSQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POSQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POSQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?
 UserUserId
 USCrazyPyro
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.