Hello,

I am using ASP and MS SQL Sever 2000 as backend database..

I am writing a SQL query which will pick up top 5 recipes details who have scored the highest marks from the following table

tblrecipes -

[u]The following points to be noted:</u>

- The table has 500 records.
- 50 of them have achieved the highest marks ie 100 marks each
- My following query is picking up top 5 records who have scored 100 marks

and getting this output:

http://img2.freeimagehosting.net/uploads/3d14a5272a.jpg


Now my question is:

On what basis its showing up this sequence of listing? I mean why the ID 89 is on the top and 136 at the bottom? Why the ID is not the other way round?

Like the following?

ID
__
178
136
89
286
340


Can someone explain please?


Thanx

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

__________________
-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

You are only ordering your query by total and not by ID in any way, therefore the order of the ID's returned will be unpredictable.

How do you want to order your results?

Cheers,

Chris

By saying "unpredictable" do you mean "random"?

I dnt want it to order any other way I just want to know why is it generating that way?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

The reason it is ordering that way is because:

The total for id 178 is lower then 136 is lower then 89 etc.

Your ids will always be listed based upon your total column so, while your total column will always be sorted lowest to highest, your IDs will always be jumbled because you are sorting on the total column.

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========

What dparsons is saying, is that if you want the IDs in ascending order then use this SQL statement:

SELECT TOP 5 ID, total, recipe_name
FROM tblrecipes
ORDER BY ID

This will order you data in ascending order by default. If you want it the other way round, then:

ORDER BY ID DESC

The result set will not be random, as if you run the query twice it will return the same result set. This may change as the data in your database changes as the db will just fetch the records and sort them by total with no regard for any of the other columns.

HTH,

Chris