Where to order the values displayed in a web page?

Hi,

I have a table in my database with several car types, and the order I want for that table is:

Car_typeA_1
Car_typeA_2
Car_typeA_3
Car_typeA_4
Other_Cars_typeA
Car_typeB_1
Car_typeB_2
Car_typeB_3
Other_Cars_typeB
Car_typeC_1
Car_typeC_2
Car_typeC_3
Car_typeC_4
Car_typeC_5
Other_Cars_typeC
...


This table is more or less always the same, but from time to time I want to add a new car type, for instance; Car_typeA_5, but this new type must be located under the last register, in the example under ‘Other_Cars_typeC#%92. So, now the order is wrong, and when I want to display these car types to a web form object, the items will appear wrong ordered.

My question is: To order the values(items) correctly, Where I have to do it? In the web page (ASP.NET) code behind, or somewhere in SQL Server (for example in the Stored Procedure that passes the value to the application)? Or maybe in the same database table..?

Thank you,
Cesar

I would like to put the 16 id between 4 and 5 id. There is a way using ‘ORDER BY#%92 into a Stored Procedure to order it in this way?

What are Sorting algorithms? How can I use them? ‘ORDER BY#%92 is a sorting algorithm?

Or perhaps the best choice would be recreate the table, set the first id to 1 and leave a gap of 10 between the id#%92s, thus later I could add a new car type well ordered.

What do you think?

Id Car_Types
1 Car_typeA_1
2 Car_typeA_2
3 Car_typeA_3
4 Car_typeA_4
5 Other_Cars_typeA
6 Car_typeB_1
7 Car_typeB_2
8 Car_typeB_3
9 Other_Cars_typeB
10 Car_typeC_1
11 Car_typeC_2
12 Car_typeC_3
13 Car_typeC_4
14 Car_typeC_5
15 Other_Cars_typeC
16 Car_typeA_5
...

Thanks

It looks as if you want to sort the results by the CarType field, alphabetically.

So this is all you need to do:

SELECT CarType FROM tblCarType
ORDER BY CarType

The order in which records are stored in the table is not relevant in a relational database. If you want the result to be ordered, you just query the table with an ORDER BY and there you have it.

Indeed my table exposed is an example, my real table must be sorted in a similar way, but it can#%92 t be sorted alphabetically because the ‘car_types#%92 name does not follow an alphabetical order.

I have a little doubt, if the order in which records are stored in the table is not relevant in a relational database, What is the utility of leaving a gap between id#%92s, setting the first id value to i.e. 1 and an autoincrement of i.e. 10?

If you have an auto-increment field, then it is meaningless in itself. You seem to need an alphanumeric identifier that you can 'translate' into "Car_TypeA_4", etc. You could put the Type apart into a foreign key table ("Type A", "Type B") and refer to that here, along with a serial number to distinguish between different cars that correspond to the type. The serial can start at 1 for each type you have in the reference table.

Sorry but I don' t understand what you mean. Would you mind giving me an example with my table example?

Table Cars:
Id Car_Types
1 Car_typeA_1
2 Car_typeA_2
3 Car_typeA_3
4 Car_typeA_4
5 Other_Cars_typeA
6 Car_typeB_1
7 Car_typeB_2
8 Car_typeB_3
9 Other_Cars_typeB
10 Car_typeC_1
11 Car_typeC_2
12 Car_typeC_3
13 Car_typeC_4
14 Car_typeC_5
15 Other_Cars_typeC
16 Car_typeA_5
...

And.. if I do this:

Car_id /Car_Type / Car_Order
1 /Car_typeA_1 /10
2 /Car_typeA_2 /20
3 /Car_typeA_3 /30
4 /Car_typeA_4 /40
5 /Other_Cars_typeA /50
6 /Car_typeB_1 /60
7 /Car_typeB_2 /70
8 /Car_typeB_3 /80
9 /Other_Cars_typeB /90
10 /Car_typeC_1 /100
11 /Car_typeC_2 /110
12 /Car_typeC_3 /120
13 /Car_typeC_4 /130
14 /Car_typeC_5 /140
15 /Other_Cars_typeC /150
16 /Car_typeA_5 /45
...

Then I write 'ORDER BY Car_Order' in my SP

Nobody knows how this can be done?

I have seen this practice many times, it is a group of options in a web form object (for example in a ddl) in which the last option is ‘Other types#%92.

For example, if you choose in a ddl the option ‘vegetables#%92, in the second ddl will appear a group of vegetables name, and if you choose the option ‘meat#%92, in the second ddl will appear a group of types of meat. For example the second ddl, if you have selected ‘vegetables#%92, would be:

Onions
Carrots
Potatoes
Spinach
Peas
Other Vegetables


And if you have selected ‘meat#%92 would be:

Chicken
Beef
Pork
Duck
Other Meats


Both, vegetables and meat products aren#%92 t sorted alphabetically, and most probably the database table can be:

Table: Supermarket_products

Product_id / Product_name / Family_num
1/Onions/1
2/Carrots/1
3/Potatoes/1
4/Other Vegetables/1
5/Chicken/2
6/Beef/2
7/Other Meats/2
8/Spinach/1
9/Peas/1
10/Pork/2
11/Duck/2


So, How this kind of dropdownlist order can be done?

I'd say most of the time an order_seq field would be added to give the administrator the opportuntiy to sort it any way they like...

e.g.

Product_id / Product_name / Family_num / order_seq
1/Onions/1/1
2/Carrots/1/2
3/Potatoes/1 /3
4/Other Vegetables/1/6
5/Chicken/2/1
6/Beef/2/2
7/Other Meats/2/5
8/Spinach/1/4
9/Peas/1/5
10/Pork/2/3
11/Duck/2/4

Then if another product gets added then either the admin or the user who added to product has the chance to resort the items which would cause the order_seq to be updated.

Cheers
Twan

Ok, I think that this is a good system.

Thank you!