MySQL sort by using a datetime file in ASP.NET

Question

MySQL sort by using a datetime file in ASP.NET

Matt Brown 1

The completed date is a date type field. If I sort on it, I get the expected results. The create date is a datetime type of field. If I do a sort on it, it doesn't sort correctly and sorts by the request ID. I've done the sort with and without casting it, and I still can't get it to work. I'm merely adding it here to show that I did try to do just that. Various Google searches have also left me with no answers. Any ideas how to make this work?

string cmdString = "SELECT requests.requestid, " +   
                                    "assignee.firstname, " +  
                                    "assignee.lastname, " +  
                                    "astatus.action, " +  
                                    "requests.createDate, " +  
                                    "requests.completionDate, " +  
                                    "requests.hlq, " +  
                                    "hlqdc.name, " +  
                                    "rtype.name as typename, " +  
                                    "module.name as xgroup, " +  
                                    "requestor.firstname as requestorfirstname, " +  
                                    "requestor.lastname as requestorlastname, " +  
                                    "requests.title, " +  
                                    "comments.comment " +  
                                "FROM app.request requests " +  
                                "Left JOIN app.user assignee ON assignee.id = requests.assignedToId " +  
                                "Inner JOIN app.action astatus ON astatus.id = requests.actionid " +  
                                "LEFT JOIN app.datacenter hlqdc ON hlqdc.id = requests.hlqDevCenterId " +  
                                "Inner JOIN app.user requestor ON requestor.id = requests.userid " +  
                                "Left JOIN app.requesttype rtype ON rtype.id = requests.requestTypeId " +  
                                "Inner JOIN app.moduletype module ON module.id = requests.moduleTypeId " +  
                                "Inner JOIN app.comment comments ON comments.requestid = requests.requestid ";  
  
            if (!string.IsNullOrEmpty(sql))  
                cmdString += "Where " + sql;  
              
            cmdString += " Order by CAST(requests.createDate AS DATETIME) limit 1000;";

AgaveJoe 30,126 Reputation points

2023-01-05T20:38:10.947+00:00

If I do a sort on it, it doesn't sort correctly and sorts by the request ID.

Correct and the expected behavior. Order is a display feature and usually handled or requested by the UI. If you need the result set ordered by date, or any field, then the ORDER BY <field> is required. Otherwise, you'll get a result set in the order the system thinks is best at the time the query us run. Usually, the result set is ordered is by a clustered index (an ID) if an ORDER BY clause is not used.

If the problem is you need a CAST to make the sort work property then I assume requests.createDate is a character field.
Matt Brown 1 Reputation point

2023-01-05T21:23:17.887+00:00

The order by statement is at the bottom so it is there. I used CAST as that was suggested elsewhere. I also used a UNIX based conversion to no avail as well. I have used just a simple order by of 'Order by requests.createDate' and it still orders it by the request ID.
Lan Huang-MSFT 30,186 Reputation points Microsoft External Staff

2023-01-06T05:13:03.41+00:00

Hi @Matt Brown ,

The completed date is a date type field. If I sort on it, I get the expected results. The create date is a datetime type of field. If I do a sort on it, it doesn't sort correctly and sorts by the request ID.

Have you tried sorting in the same way as completionDate?
It can be seen from your sql string that completionDate and createDate should be in one table.
completionDate works, createDate should work too.
Matt Brown 1 Reputation point

2023-01-09T18:17:18.457+00:00

completionDate works. createDate does not. The only difference I can tell is the completionDate is a date field as where createDate is a DateTime field.

1 answer

Your answer

AgaveJoe 30,126 Reputation points

2023-01-05T20:38:10.947+00:00

If I do a sort on it, it doesn't sort correctly and sorts by the request ID.

Correct and the expected behavior. Order is a display feature and usually handled or requested by the UI. If you need the result set ordered by date, or any field, then the ORDER BY <field> is required. Otherwise, you'll get a result set in the order the system thinks is best at the time the query us run. Usually, the result set is ordered is by a clustered index (an ID) if an ORDER BY clause is not used.

If the problem is you need a CAST to make the sort work property then I assume requests.createDate is a character field.
Matt Brown 1 Reputation point

2023-01-05T21:23:17.887+00:00

The order by statement is at the bottom so it is there. I used CAST as that was suggested elsewhere. I also used a UNIX based conversion to no avail as well. I have used just a simple order by of 'Order by requests.createDate' and it still orders it by the request ID.
Lan Huang-MSFT 30,186 Reputation points Microsoft External Staff

2023-01-06T05:13:03.41+00:00

Hi @Matt Brown ,

The completed date is a date type field. If I sort on it, I get the expected results. The create date is a datetime type of field. If I do a sort on it, it doesn't sort correctly and sorts by the request ID.

Have you tried sorting in the same way as completionDate?
It can be seen from your sql string that completionDate and createDate should be in one table.
completionDate works, createDate should work too.
Matt Brown 1 Reputation point

2023-01-09T18:17:18.457+00:00

completionDate works. createDate does not. The only difference I can tell is the completionDate is a date field as where createDate is a DateTime field.

Answer 1

Michael Taylor 60,161

Just going out on a limit and asking the question of whether you're sure the ordering is wrong? I'm going to guess that RequestId is an identity column that is sequentially incrementing each time a row is added. Furthermore the CreateDate is probably the timestamp when the row was added. Ignoring rows being added at the same time I would expect the order of the rows to be the same whether you ordered by RequestId or CreateDate. If I saw a RequestId that had a CreateDate higher than the rows after it then I would suspect it was added in a transaction but otherwise wouldn't matter.

Can you provide us the final version of the query you're running (not the code that is generating it) along with a snapshot of the data you're getting when the ordering seems wrong?

Matt Brown 1 Reputation point

2023-01-05T22:03:23.733+00:00

Yes, the order is wrong. The data is 20+ years old. ID and creation dates are not directly correlated. A user enters in a creation date into a form. The results should order them from that creation date onward as per end user requests.

I put in a creation date for the query and this is what I got. Again, I can remove the cast and still get an incorrect sort.

SELECT requests.requestid,
assignee.firstname,
assignee.lastname,
astatus.action,
requests.createDate,
requests.completionDate,
requests.hlq, hlqdc.name,
rtype.name as typename,
module.name as xgroup,
requestor.firstname as requestorfirstname,
requestor.lastname as requestorlastname,
requests.title,
comments.comment
FROM app.request requests
Left JOIN app.user assignee ON assignee.id = requests.assignedToId
Inner JOIN app.action astatus ON astatus.id = requests.actionid
LEFT JOIN app.datacenter hlqdc ON hlqdc.id = requests.hlqDevCenterId
Inner JOIN app.user requestor ON requestor.id = requests.userid
Left JOIN app.requesttype rtype ON rtype.id = requests.requestTypeId
Inner JOIN app.moduletype module ON module.id = requests.moduleTypeId
Inner JOIN app.comment comments ON comments.requestid = requests.requestid
Where requests.createDate >= '2019-12-05'
Order by CAST(requests.createDate AS DATETIME) limit 100;

Example data
ID: 1
Create Date: 12/10/2000

ID: 2
Create Date: 1/12/2001

ID: 3
Create Date: 1/14/2001

ID: 4
Create Date: 8/8/2000

ID: 5
Create Date: 10/15/2000
Michael Taylor 60,161 Reputation points

2023-01-05T22:09:17.217+00:00

What version of MySQL are you running?

What is the column type of CreateDate?

If you take your query as you provided and run it directly in the MySQL Monitor then do you get the same results or is the query correct?
AgaveJoe 30,126 Reputation points

2023-01-06T13:01:24.693+00:00
The query filters by requests.createDate

Where requests.createDate >= '2019-12-05'

The ORDER BY is using the same field. Every requests.createDate field value is 2019-12-05 due to the WHERE.

Order by CAST(requests.createDate AS DATETIME) limit 100;

Essentially there is no sort order. MySQL returns the result set in the most efficient manor which is usually by a clustered index if a clustered index exists. The reason this happens is because clustered indexes store rows sorted by the clustered index. The data is already sorted in the table.

Can you explain what you expect to happen?
Matt Brown 1 Reputation point

2023-01-09T18:15:03.963+00:00

The sort still doesn't work even if you remove the where clause. And again, I can do a sort my the completion date and that actually works.
Michael Taylor 60,161 Reputation points

2023-01-09T19:19:29.543+00:00

Does the query work if you run it directly in the MySQL UI tool? If it doesn't then it won't work in ASP.NET either. Please confirm that your query you're actually running in the code works correctly when run on the DB directly. This will tell you where the problem resides.
Matt Brown 1 Reputation point

2023-01-09T20:04:38.673+00:00

Yep, it works in MySQL Workbench.
Michael Taylor 60,161 Reputation points

2023-01-09T20:07:35.097+00:00

Please provide us the working SQL that works in the UI. You mentioned at least 2 different queries you've tried so we should ensure we are getting the query that actually works to also work in ASP.NET. Also, what version of MySQL are you running?
Matt Brown 1 Reputation point

2023-01-09T20:55:25.05+00:00

MySQL Server 5.6.

SELECT requests.requestid,
assignee.firstname,
assignee.lastname,
astatus.action as status,
requests.submitDate as 'create date',
requests.completionDate as 'completion date',
requests.hlq,
hlqdc.name,
rtype.name as typename,
module.name as 'group',
requestor.firstname as firstname,
requestor.lastname as lastname,
requests.title,
comments.comment
FROM app.request requests
Left JOIN app.user assignee ON assignee.id = requests.assignedToId
Inner JOIN app.action astatus ON astatus.id = requests.actionid
LEFT JOIN app.datacenter hlqdc ON hlqdc.id = requests.hlqDevCenterId
Inner JOIN app.user requestor ON requestor.id = requests.userid
Inner JOIN app.requesttype rtype ON rtype.id = requests.requestTypeId
Inner JOIN app.moduletype module ON module.id = requests.moduleTypeId
Inner JOIN app.comment comments ON comments.requestid = requests.requestid
order by requests.createDate limit 1000
Michael Taylor 60,161 Reputation points

2023-01-09T21:31:08.16+00:00

Let's simplify this query. Get rid of all the joins and just select from the request table and order by. Does it work? If so then add in the next join and repeat until it breaks.

A quick read online indicates that MySQL 5.7 through at least MySQL 8.0 did not work consistently with order by. There was at least one unrelated post that mentioned the optimizer failed to work when using order by with datetime values. But I have not confirmed any of this. Your version is 5.6 and I cannot find any docs before 5.7 so not sure if this was an issue before.
Matt Brown 1 Reputation point

2023-01-12T18:22:07.77+00:00

I tried it in 8.0.27 and still get the incorrect sort. I tried to simplify the query, but it is going to take some more effort and I need to handle the query outputting less data.

Brown, Matt 126

I haven't gotten this to work yet. I have started to try and get it to be sorted elsewhere. Just before the data is returned from the function which performs the query, I do this to try and order the data, but yet it doesn't. Any idea why?

objectList.Sort((x, y) => x.CreateDate.CompareTo(y.CreateDate));

I also have looked into doing a sort vis the AJAX call itself, but I haven't gotten that to work either. I have tried around 4 variations of this and have yet to get the AJAX code to work. If either of these could be resolved, I'll take that as an answer as well.

var data = $.ajax({
                type: "POST",
                contentType: "application/json; charset=unicode",
                url: '<%= ("Default.aspx/GetData") %>',
                data: "{'avzid': '" + avzid + "', 'rvzid': '" + rvzid + "', 'requestorfname': '" + requestorfname + "','requestorlname': '" + requestorlname + "','assigneefname': '" + assigneefname + "','assigneelname': '" + assigneelname + "','createDate': '" + createDate + "','cDate': '" + cDate + "','status': '" + status + "','group': '" + group + "','general': '" + general + "'}",
                dataType: "json",
                async: false,
                success: function (data) {
                    response.sort();
                    // data['results'].sort(function (a, b) {
                    //    return a.createDate - b.createdate;
                    // });
                    //$.each(data['results'], function (n, v) {
                    //    callBack(v.text);
                    //});
                }
            }).responseText.replace(/<\/?[^>]+>/gi, '');

Share via

MySQL sort by using a datetime file in ASP.NET

1 answer

Your answer