ASP.NET / SQL drop-down list sort order

Question

I am trying to correct the sort order of my ASP.NET drop down list.

The problem I have is that I need to select a distinct Serial number and have these numbers organised by DateTime Desc.

However I cannot ORDER BY DateTime if using DISTINCT without selecting the DateTime field in my query.

However if I select DateTime this selects every data value associated with a single Serial number and results in duplications.

The purpose of my page is to display data for ALL Serials, or data associated to one serial. When a new cycle begins (because it is a new production run) the Serial reverts to 1. So I cannot simply organise by serial number either.

When I use the following SQL statement the list box is in the order I require but after a period of time (usually a few hours) the order changes and appears to have no organised structure.

I'm fairly new to ASP.NET / SQL, does anyone know of a solution to my problem.

Answer 1

If you have multiple date times for each serial number, then which do you want to use for ordering? If the most recent, try this:

SELECT  SerialNumber,
        MAX(DateTimeField)
FROM    Table
GROUP BY SerialNumber
ORDER BY 2 DESC

Answer 2

Hi,

I don´t know if everybody agrees with that, but when I see a DISTINCT in a query the first thought that goes trough my mind is "This is wrong". Generally, DISTINCT is not necessary and it´s used when the person writing the query doesnt know very well what he is doing and this might be the case since you said you are new with Sql.

Without complete knowledge of your model is difficult to assist you a hundred percente, but I would say that you should use a GROUP BY clause instead of DISTINCT, then you can order it correctly.