Thread: DIfference between max() and greatest() ?
<div dir="ltr">What is the difference between these two. I know that max() is an aggregate function<br /><br />select model,count(distinctcars)<br />from rc_cars<br />where greatest(baught,returned) < current_date - integer '1'<br /> groupby model;<br /><br />Do i need to have a max () around a greatest() to make sure i get the most recent of the two.<br/><br />select model,count(distinct cars)<br /> from rc_cars<br /> where max(greatest(baught,returned)) < current_date- integer '1'<br /> group by model;<br /><br /><br /></div>
max(expression) is an aggregate function
(from the manual: "maximum value of expression across all input values")
greatest(value [, ...]) is not an aggregate function
(from the manual: "The
GREATEST function selects the largest value from a list of any number of expressions.")So max takes the maximum of values coming from several rows, while greatest takes the maximum of values coming from 1 row.
Assuming
select model,count(distinct cars)
from rc_cars
group by model
from rc_cars
group by model
returns more than one record you will need both functions.
Greatest gets the most recent date out of "bought" and "returned" PER RECORD.
Max gets the most recent date out of all these greatest dates OVER ALL RECORDS.
| Model | Bought | Returned | ||
| X | 2004-08-25 | 2005-01-01 | -> | Greatest = 2005-01-01 |
| X | 2006-02-17 | 2006-02-18 | -> | Greatest = 2006-02-18 |
| X | 2005-11-13 | 2001-05-16 | -> | Greatest = 2005-11-13 |
| ¯ | ||||
| Max = 2006-02-18 |
That being said, isn't it unlikely that "bought" is more recent than "returned"?
I can imagine that one can only return a car after buying it.
If so, writing
... WHERE max(returned) < current_date - interval '1 day' ...
would be enough.
Another thought: with this WHERE clause a car returned yesterday will not show up.
Is that what you want? If not, use
... WHERE max(returned) < current_date ...
Good luck
>>> "Ruben Gouveia" rubes7202@gmail.com> 2008-09-11 19:33 >>
What is the difference between these two. I know that max() is an aggregate function
select model,count(distinct cars)
from rc_cars
where greatest(baught,returned) < current_date - integer '1'
group by model;
Do i need to have a max () around a greatest() to make sure i get the most recent of the two.
select model,count(distinct cars)
from rc_cars
where max(greatest(baught,returned)) < current_date - integer '1'
group by model;
select model,count(distinct cars)
from rc_cars
where greatest(baught,returned) < current_date - integer '1'
group by model;
Do i need to have a max () around a greatest() to make sure i get the most recent of the two.
select model,count(distinct cars)
from rc_cars
where max(greatest(baught,returned)) < current_date - integer '1'
group by model;
Ruben Gouveia wrote: > What is the difference between these two. I know that max() is an > aggregate function ... and greatest() is a normal single-row function. One works vertically, one works horizontally, if that helps you. :-) Or max() is like sum() and greatest is like +.
Dear friends,<br /><br /> I have problem in postgresql view. This is the view (Pay attention to the red and blue colours):<br/><br /> SELECT tblpenjualan.tanggal, tblpenjualan."operator", tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher,tblpenjualan.jam, tblpenjualan.id, tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar,tbltransaksi.harga, tbltransaksi.disc1, tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori,tblkategoriproduk.kode, tblkategoriproduk.kategori, tbltransaksi.hargapokok * tbltransaksi.keluarAS modal, <font color="#ff0000"><b>tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric* (tbltransaksi.keluar * tbltransaksi.harga) AS jumlah</b></font>, <font color="#333399"><b>tbltransaksi.keluar* tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric * (tbltransaksi.keluar * tbltransaksi.harga)- tbltransaksi.hargapokok * tbltransaksi.keluar AS profit</b></font><br /> FROM tblpenjualan<br /> JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid<br /> LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text= tblproduk.kode::text<br /> LEFT JOIN tblsubkategori ON tblproduk.subkat::text = tblsubkategori.id::text<br/> LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text = tblkategoriproduk.kode::text;<br/><br /> The result is :<br /><br /><tt>"2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPSHAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.0000;<b><fontcolor="#ff0000">4200.000000000000000000000000</font></b>;<font color="#333399"><b>240.000000000000000000000000</b></font><br/> "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8991001111425";3.00;1650.00;0.00;"TAKE-ITMILK CHOCOLATE WAFER 17 G";"8";"BISKUIT";"MKN";"Makanan";4680.0000;<fontcolor="#ff0000"><b>4950.000000000000000000000000</b></font>;<b><font color="#333399">270.000000000000000000000000</font></b><br/> "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8991115000103";2.00;1600.00;0.00;"ALPENLIEBEORIGINAL 41 G ISI 10 BTR";"62";"PERMEN";"MKN";"Makanan";3000.0000;<fontcolor="#ff0000"><b>3200.000000000000000000000000</b></font>;<b><font color="#333399">200.000000000000000000000000</font></b><br/> "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8990800010533";1.00;1600.00;0.00;"MENTOSCHEWY DRAGEES 37 G";"62";"PERMEN";"MKN";"Makanan";1500.0000;<fontcolor="#ff0000"><b>1600.000000000000000000000000</b></font>;<b><font color="#333399">100.000000000000000000000000</font></b></tt><br/><br /> Why the calculation result has so many decimals?<br /><br /> Thank you<br /><br />
Hengky Lie wrote: > Dear friends, Hello. Quick tip - don't reply to another message when starting a new thread, people will assume it's part of the old thread. [snip most of query] > / 100::numeric * > Food";3960.0000;*4200.000000000000000000000000*;*240.000000000000000000000000* > Why the calculation result has so many decimals ? Because you've cast 100 to numeric, rather than numeric(10,2) or whatever. This means you get the full accuracy on the calculation - it won't ever trim a numeric (that's the whole reason to have the type). -- Richard Huxton Archonet Ltd
Thanks Bart,
Your explanation is great. Returned and Bought are of datatypes date, hence Returned would be a more recent value.
Your explanation is great. Returned and Bought are of datatypes date, hence Returned would be a more recent value.
On Fri, Sep 12, 2008 at 12:10 AM, Bart Degryse <Bart.Degryse@indicator.be> wrote:
max(expression) is an aggregate function(from the manual: "maximum value of expression across all input values")greatest(value [, ...]) is not an aggregate function(from the manual: "TheGREATESTfunction selects the largest value from a list of any number of expressions.")So max takes the maximum of values coming from several rows, while greatest takes the maximum of values coming from 1 row.Assumingselect model,count(distinct cars)
from rc_cars
group by modelreturns more than one record you will need both functions.Greatest gets the most recent date out of "bought" and "returned" PER RECORD.Max gets the most recent date out of all these greatest dates OVER ALL RECORDS.
Model Bought Returned X 2004-08-25 2005-01-01 -> Greatest = 2005-01-01 X 2006-02-17 2006-02-18 -> Greatest = 2006-02-18 X 2005-11-13 2001-05-16 -> Greatest = 2005-11-13 ¯ Max = 2006-02-18 That being said, isn't it unlikely that "bought" is more recent than "returned"?I can imagine that one can only return a car after buying it.If so, writing... WHERE max(returned) < current_date - interval '1 day' ...would be enough.Another thought: with this WHERE clause a car returned yesterday will not show up.Is that what you want? If not, use... WHERE max(returned) < current_date ...Good luck
>>> "Ruben Gouveia" rubes7202@gmail.com> 2008-09-11 19:33 >>What is the difference between these two. I know that max() is an aggregate function
select model,count(distinct cars)
from rc_cars
where greatest(baught,returned) < current_date - integer '1'
group by model;
Do i need to have a max () around a greatest() to make sure i get the most recent of the two.
select model,count(distinct cars)
from rc_cars
where max(greatest(baught,returned)) < current_date - integer '1'
group by model;
Dear Richard,<br /><br /> Sorry for that :) and thanks for your tips, but it didn't solve my problem. Maybe because my postgresqlknowledge limitation. I have tried your suggestion, my view like this :<br /><br /> -- View: "vwlapjual"<br /><br/> -- DROP VIEW vwlapjual;<br /><br /> CREATE OR REPLACE VIEW vwlapjual AS <br /> SELECT tblpenjualan.tanggal, tblpenjualan."operator",tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher, tblpenjualan.jam, tblpenjualan.id,tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar, tbltransaksi.harga, tbltransaksi.disc1,tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori, tblkategoriproduk.kode, tblkategoriproduk.kategori,tbltransaksi.hargapokok * tbltransaksi.keluar::numeric(2,0) AS modal, <font color="#ff0000"><b>tbltransaksi.keluar* tbltransaksi.harga - (tbltransaksi.disc1 / 100::numeric(3,0) * (tbltransaksi.keluar* tbltransaksi.harga))::numeric(10,0) AS jumlah</b></font>, <font color="#ff0000"><b>tbltransaksi.keluar* tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric(3,0) * (tbltransaksi.keluar* tbltransaksi.harga) - (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(10,0) AS profit</b></font><br/> FROM tblpenjualan<br /> JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid<br /> LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text = tblproduk.kode::text<br /> LEFT JOIN tblsubkategori ON tblproduk.subkat::text= tblsubkategori.id::text<br /> LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text =tblkategoriproduk.kode::text;<br /><br /> ALTER TABLE vwlapjual OWNER TO hpl;<br /><br /> and the view result like this:<br /><br /> "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPSHAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.00;<fontcolor="#ff0000"><b>4200.0000</b></font>;<font color="#ff0000"><b>240.000000000000000000000000</b></font><br/><br /> Thanks for any suggestion.<br /><br /> Regards,<br/> Hengky<br /><br /><br /> Richard Huxton wrote: <blockquote cite="mid:48CA432A.1050501@archonet.com" type="cite"><prewrap="">Hengky Lie wrote: </pre><blockquote type="cite"><pre wrap="">Dear friends, </pre></blockquote><prewrap=""> Hello. Quick tip - don't reply to another message when starting a new thread, people will assume it's part of the old thread. [snip most of query] </pre><blockquote type="cite"><pre wrap="">/ 100::numeric * </pre></blockquote><pre wrap=""> </pre><blockquotetype="cite"><pre wrap="">Food";3960.0000;*4200.000000000000000000000000*;*240.000000000000000000000000* </pre></blockquote><pre wrap=""></pre><blockquote type="cite"><pre wrap="">Why the calculation result has so many decimals ? </pre></blockquote><prewrap=""> Because you've cast 100 to numeric, rather than numeric(10,2) or whatever. This means you get the full accuracy on the calculation - it won't ever trim a numeric (that's the whole reason to have the type). </pre></blockquote><br />
On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie <hengkyliwandouw@gmail.com> wrote: > Dear Richard, > > Sorry for that :) and thanks for your tips, but it didn't solve my problem. > Maybe because my postgresql knowledge limitation. I have tried your > suggestion, my view like this : Put parens around the whole thing, like: (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal Otherwise you're really doing this: tbltransaksi.hargapokok::numeric * tbltransaksi.keluar::numeric(2,0) AS modal and when you multiply a numeric times a numeric(2,0) you get a numeric, not a numeric(2,0)
> -----Mensaje original----- > De: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] En nombre de Scott Marlowe > > On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie > <hengkyliwandouw@gmail.com> wrote: > > Dear Richard, > > > > Put parens around the whole thing, like: > > (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal > Consider that if you are NOT going to use the decimals you should really use integer or bigint datatypes. The numeric type compute much slower than integer datatypes. Regards, Fernando
On Fri, Sep 12, 2008 at 4:01 PM, Fernando Hevia <fhevia@ip-tel.com.ar> wrote: > > >> -----Mensaje original----- >> De: pgsql-sql-owner@postgresql.org >> [mailto:pgsql-sql-owner@postgresql.org] En nombre de Scott Marlowe >> >> On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie >> <hengkyliwandouw@gmail.com> wrote: >> > Dear Richard, >> > >> >> Put parens around the whole thing, like: >> >> (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal >> > > Consider that if you are NOT going to use the decimals you should really use > integer or bigint datatypes. The numeric type compute much slower than > integer datatypes. Note that if you're just converting the output the cost is minimal compared to if you're doing all your math in numeric. It's when you force math to happen that numeric is slower, but numeric's gotten a lot of tuning in the last few years and it's withing a few percentage of integer for most measurements. Definitely not twice as slow or anything like they once were.
On Mon, Sep 15, 2008 at 2:14 PM, Fernando Hevia <fhevia@ip-tel.com.ar> wrote: > > >> -----Mensaje original----- >> De: Scott Marlowe [mailto:scott.marlowe@gmail.com] > >> > >> > Consider that if you are NOT going to use the decimals you should >> > really use integer or bigint datatypes. The numeric type >> compute much >> > slower than integer datatypes. >> >> Note that if you're just converting the output the cost is >> minimal compared to if you're doing all your math in numeric. >> It's when you force math to happen that numeric is slower, >> but numeric's gotten a lot of tuning in the last few years >> and it's withing a few percentage >> of integer for most measurements. Definitely not twice as slow or >> anything like they once were. >> > > Well, in that case the manual should be revised. > > 8.1.2. Arbitrary Precision Numbers > > The type numeric can store numbers with up to 1000 digits of precision and > perform calculations exactly. It is especially recommended for storing > monetary amounts and other quantities where exactness is required. However, > arithmetic on numeric values is __very slow__ compared to the integer types, > or to the floating-point types described in the next section. > > Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html > > The explicit "very slow" assertion scared me quite enough so to avoid > numeric types where possible. yeah, I agree. In the days of 7.2 it was horrifically slow (like 10 to 20 times slower most the time) to do numeric math on similar sized numbers as int math. Note that if you are working with numbers of 500 digits it's gonna be a lot slower than numbers with 10 digits, but if the numbers are similar in size, numeric has had a lot of optimization that makes it quite usable now. which is handy when porting from oracle, where everything is a sort-of numeric.
> -----Mensaje original----- > De: Scott Marlowe [mailto:scott.marlowe@gmail.com] > > > > Consider that if you are NOT going to use the decimals you should > > really use integer or bigint datatypes. The numeric type > compute much > > slower than integer datatypes. > > Note that if you're just converting the output the cost is > minimal compared to if you're doing all your math in numeric. > It's when you force math to happen that numeric is slower, > but numeric's gotten a lot of tuning in the last few years > and it's withing a few percentage > of integer for most measurements. Definitely not twice as slow or > anything like they once were. > Well, in that case the manual should be revised. 8.1.2. Arbitrary Precision Numbers The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is __very slow__ compared to the integer types, or to the floating-point types described in the next section. Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html The explicit "very slow" assertion scared me quite enough so to avoid numeric types where possible. Regards, Fernando.