How do I sorting a column in SQL compact 4.0 by default?

Question

0.00/5 (No votes)

See more:

Hi, I am using sql compact 4.0 to view my database table. The first column is Id, which does not sort the table's rows ascendingly by default. I have added an example to show the case, where 207 and 208 come before 196.

sql compact 4 — Postimage.org[^]
How can I sort this column ascendingly by default?

What I have tried:

C#

[Key]
[Index]        
public int Id { get; set; }

But it is not working.

Posted 6-May-18 10:49am

Mazin78

Updated 6-May-18 13:00pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2018-05-06T13:02:00

Solution 1

There is no such thing as doing this "by default".

Showing sorted record is a UI function, not one the database does "by default". The order you get records from the database if the query did not explicitly say "ORDER BY" is not guaranteed. If you want the records sorted, you're going to have to specify that in the query command.

Posted 6-May-18 13:02pm

Dave Kreskowiak

Comments

Mazin78 7-May-18 5:47am

Thank you Dave. I understand that the "ORDER BY" is used if I want to show the content of the database as a "RESULT"; however, I thought there is a way to sort the content "Dynamically". I mean, when I add a row "manually", it will be showed and sorted directly without the need to use "ORDER BY" each time.

So, from your answer, I think this is not possible, right?

Sorry, my English is not really good, and I am new to programming.

Dave Kreskowiak 7-May-18 8:57am

Again, database table do not have an order to them. The ONLY time there is an order is when you include an "ORDER BY" clause.

Mazin78 8-May-18 10:38am

Thanks Dave, I have another question please. Lets assume I have a table of 23 rows, when I delete the row 23 from the table and insert a new row, the new row takes Id number 24. I mean, the table will have 1, 2, 3....21, 22 and 24. So, number 23 is not showed. How can I avoid this issue and set the new row as number 23 instead of 24? Thanks in advance

Dave Kreskowiak 8-May-18 10:41am

You're using an autonumbered column for an ID. You CAN'T avoid this. The only way to do so would be to have another column where you're assigning these values. The ID column should NEVER be a part of your business data. It's just there to uniquely identify each record in the table.

Depending on your business rules for this data, you might be able to simulate doing this numerical order value using ROWCOUNT. Google for "SQL ROWCOUNT" for documentation and examples.

But, typically, what you're referring to is just another UI thing, not actually part of the data.

Mazin78 8-May-18 11:25am

Brilliant, many thanks...