3

I'm not even sure it's possible to do this but I want to order a query based on the maximum value of one of three columns.

Example table structure: guid, column1, column2, column3

Columns 1-3 have numerical values and I want to order the select statement based on the maximum value of 1, 2 OR 3.

For example:

record column1  column2  column3    
---------------------------------
1      5        0        2
2      2        0        6
3      0        1        2

Would be ordered record 2, 1, 3 because 6 is the maximum value of the three fields across the three records, record 1 is the second and record 3 is the third.

Does this make any sense?

Thanks.

Tom
  • 4,467
  • 17
  • 59
  • 91
  • You can't `ORDER BY MAX(column1, column2, column3)`? – Gabe Sep 26 '10 at 03:00
  • TSQL (Sybase or SQL Server) is one of the few that *doesn't* support the GREATEST (and LEAST) functions... :/ – OMG Ponies Sep 26 '10 at 03:06
  • Actually I'm looking to use this under MySQL which I see does support the "greatest" function. It's kind of slow though. – Tom Sep 26 '10 at 03:10
  • @Tom: Why is this tagged "tsql" if you're using MySQL? Frankly, speed is a penalty for bad design. – OMG Ponies Sep 26 '10 at 03:14
  • @OMG: that's my bad. I always thought T-SQL referred to the language of SQL and was database independent. I'm thinking it might be faster to just store the maximum value in another field and sort on that. ftr, it's for user profiles in a database where I store when they have updated a profile, mood or uploaded a new photo. The point is to sort a list on most recently updated which would be the max timestamp value across the three fields. – Tom Sep 26 '10 at 03:16
  • @Tom: TSQL is the Microsoft SQL based imperative language. Most databases define their down - for instance Oracle has PL/SQL. But that's not what you're writing, that is just a plain SQL statement. – SamStephens Sep 26 '10 at 03:26
  • @SamStephens: Transact-SQL (TSQL) came from Sybase :) Microsoft had a partnership with Sybase to get into the database market, and they eventually split but MS kept using TSQL as the name for the extension. – OMG Ponies Sep 26 '10 at 03:40
  • @SamStephens: Thanks for clearing that up. I always knew there were language differences across platforms i.e., SQL Server, MySQL and Oracle having different functions but I thought the common language was always referred to as T-SQL. I learned something today which is always a good thing. – Tom Sep 26 '10 at 03:41

3 Answers3

4

It may be possible to do in a select query (possibly using something like case when though I'm not sure that's allowed in the order by clause itself, YMMV depending on the DBMS) but it's rarely a good idea to use per-row calculations if you want your database to scale well as tables get bigger ("not have the performance of a one-legged pig in a horse race", as one of our DBAs eloquently puts it).

In situations like this, I set up an additional (indexed) column to hold the maximum and ensure that the data integrity is maintained by using an insert/update trigger to force that new column to the maximum of the other three.

Because most database tables are read far more often than written, this amortises the cost of the calculation across all the reads. The cost is borne only when the data is updated and the queries become blindingly fast since you're ordering on a single, indexed, column:

select f1, f2, f3 from t order by fmax desc;
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • This is a good solution. Just found out about GREATEST under MySQL but it's kind of slow. May use this solution if the cost works out better. – Tom Sep 26 '10 at 03:13
  • Depends on size of table, and relative frequency of selects and inserts, but if your solution needs to scale, this is sensible. GREATEST is good enough for tables you know aren't going to grow very big. – SamStephens Sep 26 '10 at 03:28
  • It's a table of about 4500 records but this table will continually grow. Right now using GREATEST costs about 8 seconds to return the recordset which is unacceptable. I'm going to assume that the cost of updating another field with the max of these three anytime one of the three is updated is less than that. – Tom Sep 26 '10 at 03:34
1

As mentioned here, what you want is an equivalent of the GREATEST function.

In the absence of that, and assuming you've defined a UDF LargerOf to return the largest of two numbers, use

SELECT *
FROM Table
ORDER BY LargerOf(LargerOf(column1, column2), column3)
Community
  • 1
  • 1
SamStephens
  • 5,721
  • 6
  • 36
  • 44
  • 1
    Thanks, I should have mentioned that I am using MySQL which does have GREATEST. Never heard of it before today! – Tom Sep 26 '10 at 03:14
  • Hahaha, I didn't even look properly, for some reason I assume you were using TSQL. My mistake :) – SamStephens Sep 26 '10 at 03:23
  • No, I changed it to MySQL. Had it as T-SQL as I thought T-SQL was platform independent and described the language of SQL =) – Tom Sep 26 '10 at 03:32
1
create table myTable
(column1 int, column2 int, column3 int)
go


insert into myTable
values (5, 0 , 2)
go

insert into myTable
values (2, 0 , 6)
go

insert into myTable
values (0, 1 , 2)
go


select *
from mytable
order by case when column1 > column2 and column1 > column3 then column1
when column2 > column3 then column2
else column3 end desc
JBrooks
  • 9,901
  • 2
  • 28
  • 32
  • That totally works, thanks. Going to use GREATEST though, never heard of it before today =) – Tom Sep 26 '10 at 03:12
  • Greatest() is not a sql server built in function - it is Oracle. You can easily create your own, but you should still know how to do the sort with a CASE statement. – JBrooks Sep 26 '10 at 03:33
  • GREATEST is supported by MySQL and PostgreSQL, including Oracle. SQLite provides similar functionality using MAX: `MAX(col1, col2, col3)` – OMG Ponies Sep 26 '10 at 03:50