Menu
I found this some time ago and have been using it since; however, looking at it today, I realized that I do not fully understand why it works. Can someone shed some light on it for me?
ORDER BY s.type!= 'Nails', s.type!= 'Bolts', s.type!= 'Washers', s.type!= 'Screws', s.type!= 'Staples', s.type!= 'Nuts', ... If I order by s.type, it orders alphabetically. If I use the example above it uses the same order as the line positions. What I don't understand is the use of !=. If I use = it appears in the opposite order. I cannot wrap my head around the concept of this.
It would reason to me that using = in place of the !='s above would place Nails first in position, but it does not, it place it in the last. I guess my question is this: Why do i have to use !=, not = in this situation?
828
custom-order in American English (ˈkʌstəmˈɔrdər) transitive verb. to obtain by special or individual order. These wide doors have to be custom-ordered.
By default SQL ORDER BY sort, the column in ascending order but when the descending order is needed ORDER BY DESC can be used. In case when we need a custom sort then we need to use a CASE statement where we have to mention the priorities to get the column sorted.
If I order an item that is a custom order, how long will it take to receive it? After your department approves the request, fulfillment times vary based upon the vendor, item, and their current inventory. Typically, it takes 2-8 weeks for these items to be delivered.
The SQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Each expression gets evaluated as a bool and treated as 0 for false and 1 for true and sorted appropriately. Even though this works, the logic is hard to follow (and thus maintain). What I use is a function that finds a value's index in an array.
ORDER BY idx(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type) This is much easier to follow. Nails will be sorted first and nuts sorted last. You can see how to create the idx function in the Postgres snippets repository. http://wiki.postgresql.org/wiki/Array_Index
88
@Scott Bailey suggested great idea. But it can be even simpler (you don't have to create custom function) since PostgreSQL 9.5. Just use array_position function:
ORDER BY array_position(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
