[SQL] Find min and max values across two columns?
Hello. I've recently begun to use PostgreSQL in earnest (working with data as opposed to just having clever applications tuck it away in there) and have hit a wall with something. I'm trying to build a query that among other things, returns the minimum and maximum values contained in either of two columns. The problem is that min() and max() only take one column name as an argument. Is there some clever way that I can craft an expression to find the min/max across both columns? I have yet to delve into any pgsql so if it requires that, then please go easy on me. :) Example: id | columnA | columnB 1 | 4 | 2 2 | 3 | 4 2 | 5 | 1 I'd like to be able to discover that 1 is the smallest value in either columnA or columnB and 5 is the largest. I don't actually care what rows they are in, I just want the values. Thanks for your time! Any help or pointers to relevant reading material on this would be greatly appreciated. (I have been using the excellent PostgreSQL docs and an equally good book titled Beginning Databases with PostgreSQL by Neil Matthew and Richard Stones so far.) -- Amos ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Find min and max values across two columns?
Your tips were great and I have hunted down the relevant pages in the docs. Thanks guys! -- Amos On 24-Mar-06, at 4:20 PM, Rod Taylor wrote: On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote: Rod Taylor <[EMAIL PROTECTED]> writes: The reason for the subselect is to prevent multiple calculations of individual column aggregates. I believe it *may* be calculated multiple times otherwise this would work just as well: select case when max(a) > max(b) then max(a) else max(b) end as max from tab; Just for the record, we've gotten that right since 7.4. greatest() would be a notationally cleaner solution than CASE, but multiple occurrences of identical aggregates don't cost much of anything. Thanks. I could not remember one way or the other. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
