CODEWITHSUNDEEP
sqldatabasesql-order-by
Nishant
Nishant

Reputation: 21914

Order By Date when the Date is converted to MM/DD/YY

I have a query (in pseudo) language like this .

SELECT * FROM TMP
(
SELECT 
NAME , 
Format([CREATE_DATE],"dd/mm/yyyy") AS CREATE_DATE
FROM 
TABLE 
)TMP
ORDER BY CREATE_DATE

CREATE_DATE is stored in the DB in DD/MM/YY/HH/SS/MS format .

The inner table already loses the time stamp since we are converting to DD/MM/YY . Now what I have a bunch of records of the same date .

The question is will the result always be same if we query this again and again and we have say over 5000 records ? The Order By just has many records of the same date , hence my question . The real records however have time stamp which gets truncated after the first query .

I think its possible to actually ask this question is a more simplified manner . Supose we have a n number of records with the same Value and I do a ORDER BY over the same value , will SQL show the same result all the time ? Does it arrange by the PRIMARY_IDN in any case to make sure results are in the same order ?

Very specifically speaking , we have had a issue with related to such a query and reported in ORACLE but not in SQL , could that have any signifance ? That is probably its a bit random(I am scared to used this word) in ORACLE while SQL isn't in this particular context? I am just trying to understand .

Upvotes: 0

Views: 130

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can do this:

SELECT <columns that you want> FROM
(
SELECT 
NAME , 
Format([CREATE_DATE],"dd/mm/yyyy") AS CREATE_DATE, create_date as orig_createDate
FROM 
TABLE 
)TMP
ORDER BY orig_create_date

That is, just pass in the original date from the subquery and use it for ordering. You don't have to include it in the output.

Your final question is about a sort being "stable". That is, when rows have the same value will they be ordered in the same order. The answer is no. Databases generally do not guarantee that order by use a stable sorting algorithm. So they rows could be in a different order.

Upvotes: 1

Related Questions