I am using a table which has Createdon and Lastmodifiedon fields, I have to display first the newly added record based on createdon and then followed by lastmodifiedon records using mysql. I have tried adding "ORDER BY Createdon,Lastmodifiedon desc" at the end of the query;but sorting based on first column only occurs. Anyone please help in this issue
Thanks.
You are sorting Createdon in ascending order, so newer records appear at the end:
ORDER BY Createdon,Lastmodifiedon desc
You need:
ORDER BY Createdon DESC, Lastmodifiedon DESC
This is a general issue with sorting by date columns. Because a date column normally includes a time accurate to a fine resolution (sometimes milliseconds), anything later in the sort order is effectively disregarded because the probability of getting two identical items in the first date is, for most applications, too low to see it's effect.
What you therefore need to do is to truncate the initial date to a resolution you're happy with (for example, 1 day) and order by that, at which point you get identical items in the ORDERing and your second clause comes into play.
I don't know MySQL well enough to know the exact syntax for this here, but on SQL Server the simplest way I know would be
SELECT * FROM Table
ORDER BY FLOOR(CAST(CreatedOn AS Float)), LastModifiedOn DESC
Which would give you a resolution of 1 day - the reason being that SQL Server dates when converted to floats give an integer portion that represents the day. If you wanted a different resolution, you can use DATEADD and DATEDIFF in the ORDER BY statement or convert the date to a string in an ASCII sortable format (e.g. YYYYMMDD hhmmss) and truncate at the appropriate place.
In what way is this data behaving odd? E.g. Can you provide some examples of how it's behaving odd and what you expect to see and what you actually get?
The ORDER BY you are using is what is normally used and basically works along the premise that it will order by the first in the list and if it finds two the same, it'll order by the second on the list. The normal good example is Firstname & Surname. If you order by Surname, you get potentially two 'Smith's, at which point, you'd order by Firstname so that Alex appeared before Sheridan.
In your example you want to order by CreatedOn, followed by ModifiedOn. It'll order by created date and if two were created at the same datetime (or just date), then it would sort them out by Modified Date.
The solution? Well, if you keep your data nice, so that ModifiedOn is empty, then you could flip the sort and do something like: ORDER BY ModifiedOn, CreatedOn. This does leave you with one pickle though: Your ModifiedOn dates will need to start at the oldest as an empty date is considered to be an old date (something like 1/1/1970). To flip this over properly is more than a simple order by I think. To suggest an better answer would therefore probably require a bit of data to make sure the query is correct :)
If your LastModifiedOn has initial value of NULL when the record is newly created, do this:
ORDER BY
-- 1 displays first the newly created, 2 displays all modified in last order
CASE WHEN LastModifiedOn IS NOT NULL THEN 2 ELSE 1 END,
COALESCE(LastModifiedOn, CreatedON) DESC
I used created and lastmodified date the same value when adding new record and used order by lastmodified desc when showing in the grid. Thanks for all your reply.
