In many cases when you're working with SQL queries it's very important to have some formatted data when it comes back. One of the ways it can be formatted is you can see things in a specific order. For example right now if I were to just say SELECT guides_title FROM guides and if I run this query it's going to bring back in all the results.

The results are going to just sort it by the id that's the default that SQL runs. So if I run this code you can see that it has “my blog”, “something else”, “My Great post”, “my blog”, and “my blog” and that may be fine. However, we also have the ability to add a ORDER BY clause which will allow us to sort the data as it comes through and the result set.

So in order to do that we're going to add another line and say “ORDER BY” and then you pick out the parameter that you want to order it by. So here I'm going to say guides_title and then you say if you want it to be descending or ascending. We will do descending first.

Now if I run this code you can see that now this is sorted this and it has “something else”, “My Great post”, and “my blog”. This is all sorted accurately. If I were to copy this and sort ascending, It's going to be the same results set. It's just going to be flip-flopped. So if I run it again now you can see that it is alphabetically sorted.

Now I want to show you something that may seem a little weird. If I paste this in and say that I want the guides_title but even before that I want to have the guides_revenue. Then I actually want to sort by revenue and I'll try to do it descending. Now if I run this query it will work in regards to it doesn't throw an error.

However I want you to notice something a little bit weird here. So by default the way that MySQL works is it's going to process this and it's going to say 750,750, 500, 1300, and then a 1000. Well that makes no sense whatsoever. If we're sorting by descending we would expect to be able to see the largest numbers all the way down to the smallest ones. Now If I switch this to ascending and process this you can see that it has more weird behavior.

This is definitely not what you'd expect. When I was first learning SQL this part was pretty confusing to me.

The issue is that when we try to order by something as alphanumeric like guides_title that works perfectly fine. However guides_revenue If you remember if you look at the schema, the way that we structured this was a little bit different. So if we go to our tables for guides and if I look at the schema inspector in columns.

You can see that we have this integer with 11 VARchar right here. So the way this is it's not really from a revenue perspective. This doesn't really make any sense because it's trying to just take all of the items and it's treating them like alphanumeric values, even though they're really numbers.

This is a cool thing and this is part of the reason why we have this set up with this type of data type. I want you to see that are going to be many times when you're working with things like legacy data applications. Where the item in that column data attribute isn't what you'd expect. Right here guides_revenue is set to a varchar.

Probably not what you'd expect for something like revenue But there are going to be times when you have to deal with that. The way that you can fix that is by doing what's called CASTing. So what casting does is it gives you the ability to change the datatype on the fly of the values coming in.

Here I'm going to say cast as unsigned and then we'll do ascending. What this is going to do is it's going to bring these values in and instead of treating them just like alphanumeric characters. It's going to cast it as an unsigned data type which means it's going to look at it like a number and that's what unsigned represents.

So now let's run this and I process it now you can see that now this is working.

So when I have it ascending the lowest number here is at the top and then the bottom has the largest number. So we have 500, 750, 750,1000, and then 1300. Same rules apply.
Now I can use a descending run this query.

Now you can see it works exactly the way that you'd anticipate.

That's something to keep in mind whenever you're working with the data that is not ordering right, not summing properly or something like that. Then that is going to be something that you can use the cast system for. Where you're able to cast the attribute cast that call on to a different type of data. Now you can use order by the way that you'd expect.

Code

USE devcamp_sql_course_schema;

SELECT guides_title
FROM guides
ORDER BY guides_title DESC;

SELECT guides_title
FROM guides
ORDER BY guides_title ASC;

SELECT guides_revenue, guides_title
FROM guides
ORDER BY CAST(guides_revenue AS UNSIGNED) DESC;