Why form dropdown items are sorted alphabetically?

Question

Why do both SSMS and ASP.NET MVC dropdowns show the data I filled in a different order sorted alphabetically?

I've added the "Other" option as the last and when I ask SSMS to show the table content I get it sorted "Other" is not the last in the list.

The same is true for the dropdown in our ASP.NET MVC website pages:

Do I need really to add a table column with an integer value to get the items sorted in the natural order I manually filled it?

There is no "natural order", a table is an unordered set – Charlieface Jun 23 '22 at 11:06 — Charlieface, Jun 23 '22 at 11:06

score 2 · Accepted Answer · answered Jun 23 '22 at 11:05

A relational database table is by definition an unordered set of rows. Although SQL Server might return ordered data (depending on the execution plan and other factors), an ORDER BY clause in the query is required to guarantee the result set is sorted as desired.

The implication is you need column(s) in the table to specify the order. You could add an incremental column (IDENTITY, datetime, etc.) and specify that in the ORDER BY clause.

Rather than rely on insertion order, consider adding a MajorDisplaySequence column or similar. That way, you can add more rows later (MajorDisplaySequence value 1) yet still retain 'OTHER' (MajorDisplaySequence value 2) as the last returned value.

CREATE TABLE dbo.industry(
    industryId char(3) NOT NULL 
        CONSTRAINT pk_industry PRIMARY KEY
   ,industryName varchar(50) NOT NULL
   ,MajorDisplaySequence int NOT NULL
);

SELECT industryId, industryName
FROM dbo.industry
ORDER BY MajorDisplaySequence, industryId;

Sorting in client code is also option if the source data contains the needed attribute(s).

score 0 · Answer 2 · answered Jun 23 '22 at 10:42

0

I'd guess that your database table has a clustered index on it, and that is causing it to store the data in a sorted order.

If you need things to be sorted in a particular way, you may need to have a separate column to store that - an auto incrementing column, or a datetime for example - and then have an order by xxx in the query that gets the data.

answered Jun 23 '22 at 10:42

DaveShaw

52,123
16
112
141

Thanks. So adding a column for sorting is common practice? Or I should try removing the clustered index? Would the latter affect the DB query speed? – abenci Jun 23 '22 at 10:49
There's a whole topic of DB design here. If this is just a small list, you could try a heap table (no clustered index), or add an identity column and make that the clustered index. It depends on the data and access patterns, and is probably a bit much too cover in comments. – DaveShaw Jun 23 '22 at 11:00
No - as already noted, rows in a table (and therefore rows in a resultset) have no inherent order. If you want a particular order, the query forming that resultset must have an ORDER BY column. Without that, the database engine is free to return rows in any order though any index (esp. the clustered index) can influence (but not guarantee) that. – SMor Jun 23 '22 at 11:17

Why form dropdown items are sorted alphabetically?

2 Answers2