-1

I have a set of data which has the following format:

LocalDate  LocalTime SourceID  UtcTimestamp
04/30/2013 09:05:10  sourceID1 2013-04-29 23:05:00.707
04/30/2013 09:34:10  sourceID2 2013-04-29 23:34:00.707
04/30/2013 09:10:56  sourceID3 2013-04-29 23:10:00.067
01/17/2014 10:41:31  sourceID4 2014-01-17 00:41:31.147

I need to sort the above data in the descending format. My first attempt was to 'ORDER BY'the LocatDate and then LocalTime. However, the data type for 'LocalDate' and 'LocalTime' is varchar. Then I attempted the following since UtcTimestamp data type is datetime:

SELECT TOP 10000 ManagementRecords.LocalDate, ManagementRecords.LocalTime, ManagementRecords.SourceSUID, ManagementRecords.UtcTimestamp 
FROM DB.dbo.ManagementRecords ManagementRecords  
ORDER BY ManagementRecords.UtcTimestamp DESC

However the above does not work. The 'Executing query' process never returns anything. Why? I would really appreciate some help on this.

Alexander
  • 105,104
  • 32
  • 201
  • 196
SSF
  • 915
  • 5
  • 23
  • 47
  • 1
    The query you posted isn't the one you actually use, as it has a typo that would prevent it from executing (specifically, you use the alias `table` in both the `SELECT` and `ORDER BY`, but your `FROM` clause assigns the alias `tables` (plural). If you want us to help solve problems with your SQL statement, copy and paste the **actual** SQL statement and not something you make up on the fly or type in manually (where you can introduce typos or incorrect syntax). – Ken White Sep 01 '15 at 00:34
  • Thanks I will edit it now. Thanks for pointing that out. – SSF Sep 01 '15 at 00:36
  • When I get rid of 'ORDER BY' statement I get a result back for the query, however when I add the 'ORDER BY' I get nothing. – SSF Sep 01 '15 at 00:40
  • 1
    If you wait long enough you'll get something. The issue is not that you get nothing. The issue is that it performs badly. The other issue is that you are storing dates in varchar fields. – Nick.Mc Sep 01 '15 at 02:56
  • I think you are right, It's due to poor performance of the ORDER query. – SSF Sep 01 '15 at 03:21
  • Your other problem is it isn't going to sort as you expect. 04/30/2013 is going to come before 05/30/2000. Anyway the quickest and easisest fix is create a clustered index on those columns. Then the data is already ordered in that way. But that has other complications. – Nick.Mc Sep 01 '15 at 22:24

2 Answers2

1

Have you looked at the execution plan that is generated by your sql? If you're ordering by a non indexed column, then it might be doing a full table scan. Depending on how large your table is this might be what is taking a while for results to come back.

If you're able to modify the table you can try the following to add the index:

CREATE NONCLUSTERED INDEX ManagementRecords_TimeStamp ON DB.dbo.ManagementRecords ManagementRecords(UtcTimestamp);
Mumbo
  • 134
  • 4
  • I believe you are right, I am looking at the execution plan right now. How do I identify which table is missing indexing in a case where multiple tables are being quried? – SSF Sep 01 '15 at 03:23
  • ... where multiple tables are being queried? That would have to be a different question wouldn't it? this question relates to a single table query – Paul Maxwell Sep 01 '15 at 05:43
0

For your second approach, you use an alias for the table as tables, so if you want to explicitly refer table_alias.column, use tables, the alias you assigned to the table. But in your case, I think you do not need to use alias.

And for sorting by localDate, localTime, use convert(date, localDate, 1) function. So sum it up, your query should look like as following: select * from db.dbo.[table] order by convert(date, localDate, 1)

Andrew
  • 17
  • 4
  • Thank you. I noticed that. I have edited the statement. In my original statement that was not the issue, since when I get rid of 'ORDER BY' statement I get the result back for the query, however when I add the 'ORDER BY' I get nothing. – SSF Sep 01 '15 at 00:42