StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
4477099
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-12-18T07:18:40.147
FavoriteCount
0
LastActivityDate
2010-12-18T07:18:40.147
LastEditDate
LastEditorUserId
0
OwnerUserId
280746
ParentId
4285504
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
Ok, this solution is ugly, and not for the faint of heart. I haven't tested on SQL CE, but it only uses basic t-sql so it should be ok. You will have to create a <a href="http://www.sqlservercentral.com/articles/T-SQL/62867/" rel="nofollow">tally table</a> (just run his first block of code, if you don't want to read it. It uses the tempdb so you'll want to change that), and a table to hold each letter of the alphabet (due to lack of pattern matching functions). After creating the tally table (you don't have to go all the way to 11000 as the example shows), run these, and you'll see the sorting behavior you want Create the alphabet table (temp for demo purposes): <pre><code>select * into #alphatable from ( select 'A' as alpha union all select 'B' union all select 'C' union all select 'D' --etc. etc. ) x </code></pre> Create a tree table (temp for demo purposes): <pre><code>select * into #tree from ( select 'aagew' as TreeNumber union all select '3' union all select 'bsfreww' union all select '1' union all select 'xcaswf' ) x </code></pre> The solution: <pre><code>select TreeNumber from ( select t.*, tr.*, substring(TreeNumber, case when N > len(TreeNumber) then len(TreeNumber) else N end, 1) as singleChar from tally t cross join #tree tr where t.N < (select max(len(TreeNumber)) from #tree) ) z left join #alphatable a on z.singlechar = a.alpha group by TreeNumber order by case when max(alpha) is not null then 0 else TreeNumber end </code></pre> This is basically the technique that Moden describes as "Stepping through the characters", then each character is joined on the alpha table. Rows with no row in the alpha table are numeric. 
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POSorting Alphanumeric field in SQL CE (Compact Edition) version 3.5
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USjlnorsworthy
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POSorting Alphanumeric field in SQL CE (Compact Edition) version 3.5
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. This table or related slice is empty.

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.