![]() Hence, if you would like your results plotted in reverse order, DESC is the keyword to add at the end of the ORDER BY clause. ![]() The alternative is to use DESC, which is abbreviated from “descending”. Therefore, if we add ASC at the end and re-run the query, we will obtain the same output. If this keyword is not attached to the statement at the end, SQL will implicitly understand you want things ordered precisely in ascending order anyway. The first one is ASC, abbreviated from “ascending”, requiring the output to be sorted by the values in the designated field in ascending order. One of two specific reserved words can be attached at the end of the ORDER BY clause. SELECTĪs you can see in the picture above, the entire list was reorganized in alphabetical order, according to the field containing employee names. So, how can you do that? When to Use the SQL ORDER BY clauseĪdding “ ORDER BY first name” at the end of this query will provide the desired outcome. As shown in the picture below, the list we get is automatically ordered based on employee numbers.Īssume your boss has just asked you to order the people by first name, instead of by employee number. Let’s see what happens when we retrieve all records from the “ employees” table. However, in order to fully understand how to work with it, you should check out what operators we will be using first. Use it with an ORDER BY clause to sort the rows in ascending order by date.If you want to refine your output when coding, the SQL ORDER BY clause is an awesome way to go about it. ![]() STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d') %Y stands for year, %M stands for month (its full name, not a number), and %d stands for day. The second argument of this function is the date format. Then, you need to convert this string to a date using the STR_TO_DATE(date_string, '%Y %M %d') function. Since you'd like to get a string in the 'Year Month Day' format, the arguments are exam_year, exam_month, exam_day, and the spaces between them. You don't need to cast numbers to strings. The CONCAT() function combines all the arguments into one string. But first, you need to create a string using the CONCAT() function:ĬONCAT(exam_year, ' ', exam_month, ' ', exam_day) If you have a date stored as a string in the 'Year Month Day' format, you can cast it to a date using STR_TO_DATE(date_string, '%Y %M %d'). To do this, use the STR_TO_DATE() function. ![]() To sort by date, create date values from the year, the month, and the day values. The result looks like this (the rows are sorted in ascending order by exam_year, exam_month, and exam_date): subject ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d') The months are given in names, not in numbers. The exam table has the following columns: subject, exam_year, exam_month, and exam_day. Also, the rows with the same exam_date are displayed in random order (you may see Science third and Health fourth, or Health third and Science fourth). Note that in MySQL, NULLs are displayed first when sorting in ascending order and last when sorting in descending order. If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.). This way, you'll sort the data in ascending order by this column. Use the ORDER BY keyword and the name of the column by which you want to sort. The result looks like this (the rows are sorted in ascending order by exam_date): subject The exam table has two columns, subject and exam_date. You want to sort the rows by date in MySQL.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |