| Author |
Topic |
|
kangaroo_m
Starting Member
9 Posts |
 Posted - 2011-05-06 : 11:37:39
|
| I have a table of products like so;Name | Age | Priceitem1 | 2001 | 200item2 | 2006 | 100item3 | 2003 | SOLDitem4 | 2004 | 300I would like to order the results so the 'SOLD' item is last and the other items are ordered by their age, like so;Name | Age | Priceitem2 | 2006 | 100item4 | 2004 | 300item1 | 2001 | 200item3 | 2003 | SOLDIs it possible to do this with one SQL statement? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-06 : 11:43:08
|
| SELECT * FROM myTableORDER BY CASE WHEN Price='SOLD' THEN 1 ELSE 0 END, Age DESC |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-06 : 11:44:40
|
order by clause can be like so:order by case when Price = 'SOLD' then 1 else 0 end, Age desc I am so tempted to lecture you on the data type for the Price column. I might have said something like "From the looks of it, it looks like it is varchar. That is not a good idea" etc. But, I will resist. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-06 : 11:45:21
|
 I knew I shouldn't have lectured! I could have beaten Rob if I had listened to myself!! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-06 : 11:54:51
|
 You'll reach a point, around 1000-1500 posts, where little things like wrong data types don't bother you anymore. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-06 : 12:13:44
|
| Rob,yer bad! |
|
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-05-06 : 12:24:25
|
| Thanks for the help. I seem to have stumbled upon a rivalry. I hope my question hasn't caused any friction. :)Not sure I dare ask, but........why is a varchar a bad idea? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-06 : 12:46:01
|
| Au contraire, it's all playful banter, kangaroo, no worries!! Both Rob and I responded to your post, and mine got posted a tiny bit later than his reply.Regarding the varchar column: The column name indicates and it probably is a column whose purpose is to store price. So, it should be numeric column such as int, float, decimal etc. Having it as a varchar column can cause problems, for example:1. Someone can, perhaps unintionally, insert a completely unrelated thing such as "Item7" into the price column. That would cause untold miseries later when you try to query the data.2. If you want to sort it by price, for example to find the lowest price, it would not sort as you expect. If you had just two prices in it: 9 and 57, and your query said "order by price" it would show 57 first followed by 9, suggesting that 57 is lower than 9. This happens because varchar columns are sorted as a string and 5 comes before 9 in a string sort.In addition to those practical considerations, using the Price column to also store the status of the inventory (such as "SOLD") would be violating some of those normal forms and other rules that the likes of Cobb and Date have laid down. Database purists can tell you precisely why that is important, but it is important. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-06 : 13:31:56
|
quote:
other rules that the likes of Cobb
Should be Codd...Ted Codd: http://en.wikipedia.org/wiki/Edgar_F._Codd |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-05-06 : 20:41:53
|
| Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. Let me try to make up for your rudeness and fundamentally wrong narrative. If you had a proper table, would it look like this? CREATE TABLE Products (upc CHAR(13) NOT NULL PRIMARY KEY, initial_year INTEGER NOT NULL CHECK (stock_year > 1900) product_price DECIMAL (8,2) NOT NULL CHECK(product_price > 0.00));>> item1 | 2001 | 200.00item2 | 2006 | 100.00item3 | 2003 | SOLD <== total wrong, stupid non-relational item4 | 2004 | 300.00 <<Columns in a table are scalar values drawn from a domain. This is part of the definition of First Normal Form. So what numeric value is “SOLD”? Can it divide by zero? What is (SOLD + 1)? This FUNDAMENTAL error is called overloading. We need a product status. >> I would like to order the results so the 'SOLD' item is last and the other items are ordered by their age, like so; <<Tables have no ordering by definition. If I used this in a book, nobody would believe it. As one of the people who wrote the SOL Standards, please stop programming. There is no such thing as a price – there are purchase, sale, asking and other attribute properties. Again, this is basic data modeling. What are you trying to do? Since you cannot explain it in RDBMS, data modeling, etc. terms, can you do it in English?--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-06-11 : 12:45:53
|
| Are you serious jcelko?? I got an answer to my question in under 6 minutes. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-05 : 07:44:02
|
quote:
Originally posted by kangaroo_m
Are you serious jcelko?? I got an answer to my question in under 6 minutes.
But you haven't bought his books, right?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
How can I order results by set criteria