Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eu1zT-0003Gf-1D for pgsql-sql@arkaria.postgresql.org; Thu, 08 Mar 2018 20:18:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eu1zR-0007SH-QZ for pgsql-sql@arkaria.postgresql.org; Thu, 08 Mar 2018 20:18:13 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eu1zR-0007S7-Bw for pgsql-sql@lists.postgresql.org; Thu, 08 Mar 2018 20:18:13 +0000 Received: from mx0b-0021dc01.pphosted.com ([148.163.152.220] helo=mx0a-0021dc01.pphosted.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eu1zI-0006wC-Tg for pgsql-sql@lists.postgresql.org; Thu, 08 Mar 2018 20:18:11 +0000 Received: from pps.filterd (m0096563.ppops.net [127.0.0.1]) by mx0b-0021dc01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w28K60SY000880; Thu, 8 Mar 2018 14:17:59 -0600 Received: from mail.rglholdings.com (dns152.b.register.com [98.102.82.53] (may be forged)) by mx0b-0021dc01.pphosted.com with ESMTP id 2gfpqkaqet-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Thu, 08 Mar 2018 14:17:59 -0600 Received: from mail.rglholdings.com (localhost [127.0.0.1]) by mail.rglholdings.com (Postfix) with ESMTPS id 8D012234CF5; Thu, 8 Mar 2018 14:17:58 -0600 (CST) Received: from localhost (localhost [127.0.0.1]) by mail.rglholdings.com (Postfix) with ESMTP id 7F6B1158CE0; Thu, 8 Mar 2018 14:17:58 -0600 (CST) X-Virus-Scanned: amavisd-new at rglholdings.com Received: from mail.rglholdings.com ([127.0.0.1]) by localhost (mail.rglholdings.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id K3cPL5kET1u9; Thu, 8 Mar 2018 14:17:58 -0600 (CST) Received: from mail.rglholdings.com (mail.ipa.rglholdings.com [10.1.20.35]) by mail.rglholdings.com (Postfix) with ESMTP id 628901587F6; Thu, 8 Mar 2018 14:17:58 -0600 (CST) Date: Thu, 8 Mar 2018 14:17:58 -0600 (CST) From: Stanton Schmidt To: "David G. Johnston" Cc: Jeff Fletcher , Martin =?utf-8?Q?St=C3=B6cker?= , pgsql-sql Message-ID: <182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com> In-Reply-To: References: <415803152.48510563.1520535505737.JavaMail.zimbra@rglholdings.com> <557870630.48523473.1520536627693.JavaMail.zimbra@rglholdings.com> Subject: Re: Query question MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_25fdee6d-d1a1-4060-a1b4-5941727bdaa9" X-Originating-IP: [10.1.20.35] X-Mailer: Zimbra 8.7.9_GA_1794 (ZimbraWebClient - GC65 (Win)/8.7.9_GA_1794) Thread-Topic: Query question Thread-Index: Xxm/VWrRZgsD87vrhG+5+VVeSB/52A== X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-03-08_11:,, signatures=0 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 priorityscore=1501 malwarescore=0 suspectscore=8 phishscore=0 bulkscore=0 spamscore=0 clxscore=1011 lowpriorityscore=0 mlxscore=0 impostorscore=0 mlxlogscore=999 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1711220000 definitions=main-1803080221 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_25fdee6d-d1a1-4060-a1b4-5941727bdaa9 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable I should have mentioned which version I am using. We are at version 9.2 and= the Lateral was not introduced until version 9.3.=20 The partitioning worked Thanks.=20 stanton schmidt=20 Database Administrator=20 direct. [ callto:920.884.1281 | 920. ] 471.4495 cell 920.660.1828=20 RGL=20 GO AHEAD. ASK WHAT IF.=20 [ http://www.rgllogistics.com/ | www.RGLlogistics.co m ]=20 From: "David G. Johnston" =20 To: "Jeff Fletcher" =20 Cc: "Stanton Schmidt" , "Martin St=C3=B6cker" , "pgsql-sql" =20 Sent: Thursday, March 8, 2018 1:28:31 PM=20 Subject: Re: Query question=20 We prefer to avoid top-posting on these lists but I'll go with the flow her= e...=20 =20 select *=20 from (select distinct equipment_id from log_table) a,=20 lateral get_log_data_for_equipment(equipment_id, 5)=20 then write the get_log_data_for_equipment function as dynamic sql substitut= ing the desired limit value.=20 Not saying the above is going to be well performing but in terms of simplic= ity of the top-level query it scores high.=20 If you have an "equipment" table, which you should, replacing the select di= stinct with select should be considered. The presence of "distinct" (withou= t 'on') is a code smell for me.=20 David J.=20 On Thu, Mar 8, 2018 at 12:20 PM, Jeff Fletcher < [ mailto:jeff.fletcher@gma= il.com | jeff.fletcher@gmail.com ] > wrote:=20 Partition by is your friend...=20 [ https://urldefense.proofpoint.com/v2/url?u=3Dhttps-3A__stackoverflow.com_= questions_1124603_grouped-2Dlimit-2Din-2Dpostgresql-2Dshow-2Dthe-2Dfirst-2D= n-2Drows-2Dfor-2Deach-2Dgroup&d=3DDwMFaQ&c=3DeLkx2stMcShI0L6xvxICXHnFB9zmDv= ZmvnhsCd8Gf8M&r=3DEWbtHQpXIg2XbQduIylzyXrAFdbjWaMyy-p_LkyfjhQ&m=3DDEdLcsFrD= yuVKOvYidkUgnvb5VlWOA5TPoLM_4cEMbI&s=3DPGsyF58Wm2BowXszKbPpE1wx40C7KcKhewcE= IXUILas&e=3D | https://stackoverflow.com/questions/1124603/grouped-limit-in= -postgresql-show-the-first-n-rows-for-each-group [stackoverflow.com] ]=20 On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt < [ mailto:sschmidt@rgllogi= stics.com | sschmidt@rgllogistics.com ] > wrote:=20 BQ_BEGIN So far I have been unable to figure out how to do that.=20 I tried:=20 select a.equipment_id, b.log_date, b.log_time, b.event_desc=20 from (select distinct equipment_id from log_table) a=20 , (select equipment_id, log_date, log_time, event_desc from log_table order= by log_date desc, log_time desc limit 5) b=20 where a.equipment_id =3D b.equipment_id=20 but all I end up with is 5 total records.=20 Stanton=20 From: "Martin St=C3=B6cker" < [ mailto:martin.stoecker@stb-datenservice.de = | martin.stoecker@stb-datenservice.de ] >=20 To: "pgsql-sql" < [ mailto:pgsql-sql@lists.postgresql.org | pgsql-sql@lists= .postgresql.org ] >=20 Sent: Thursday, March 8, 2018 1:07:30 PM=20 Subject: Re: Query question=20 My first idea is to select all equipments and lateral join them to the 5 mo= st recent events=20 Regards Martin=20 Am 08.03.2018 um 19:58 schrieb Stanton Schmidt:=20 BQ_BEGIN Hi,=20 I am new to the list so feel free to let me know if I am out of line.=20 My question is:=20 I have a table that has log events for pieces of equipment. For each piece = of equipment this table may contain 1 or more (hundreds potentially).=20 I need to write a query that will return only the last 5 log events for eac= h (and every) piece of equipment.=20 log_table (=20 equipment_id character(30),=20 log_date date,=20 log_time time,=20 event_desc text=20 )=20 Thanks for your help.=20 stanton schmidt=20 Database Administrator=20 direct. [ callto:920.884.1281 | 920. ] 471.4495 cell [ tel:(920)%20660-1828= | 920.660.1828 ]=20 RGL=20 GO AHEAD. ASK WHAT IF.=20 [ http://www.rgllogistics.com/ | www.RGLlogistics.co m ]=20 BQ_END BQ_END --=_25fdee6d-d1a1-4060-a1b4-5941727bdaa9 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
I should have mentioned which version I am = using.  We are at version 9.2 and the Lateral was not introduced until= version 9.3.

The partitionin= g worked Thanks.

stanton schmidt
Database Administrator
direct. 920.471.4495  cell 920.660.1828

RGL
= GO AHEAD. ASK WHAT IF.<= /span>
www.RGLlogistics.co m
<= /div>

From: "David G. Johnston" <david.g.johnston@gma= il.com>
To: "Jeff Fletcher" <jeff.fletcher@gmail.com>Cc: "Stanton Schmidt" <sschmidt@rgllogistics.com>, "Martin St= =C3=B6cker" <martin.stoecker@stb-datenservice.de>, "pgsql-sql" <pg= sql-sql@lists.postgresql.org>
Sent: Thursday, March 8, 2018 1:= 28:31 PM
Subject: Re: Query question

We prefer to avoid top-posting on these lists but I'll go wit= h the flow here...

<no= t tested, syntax is close but might have errors>
select *
from (select distinct equipment_id from log_table) = a,
lateral get_log_data_for_eq= uipment(equipment_id, 5)

<= /div>
then write the get_log_data_fo= r_equipment function as dynamic sql substituting the desired limit value.

Not saying the above is going to be well performing but = in terms of simplicity of the top-level query it scores high.

If you have an "equipment" table, which you should, replacing the se= lect distinct with select should be considered.  The presence of "dist= inct" (without 'on') is a code smell for me.

David J.


On Thu, Mar 8, 2018 at 12:20 PM, Jeff Fletcher <jeff.fletcher@gmail.com><= /span> wrote:
=


On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt <ssch= midt@rgllogistics.com> wrote:
So far I have been unable to figure out how to = do that.

I tried:
select a.equipment_id, b.log_date= , b.log_time, b.event_desc
from (select distinct equipment_id fro= m log_table) a
, (select equipment_id, log_date, log_time, event_= desc from log_table order by log_date desc, log_time desc limit 5) b
<= div>where a.equipment_id =3D b.equipment_id

but all I end up = with is 5 total records.

Stanton
From: "Martin St=C3=B6cker= " <martin.stoecker@stb-datenservice.de>
To: "pgsql-sql" &= lt;pgsq= l-sql@lists.postgresql.org>
Sent: Thursday, March 8, 2018 = 1:07:30 PM
Subject: Re: Query question

My first idea is to select all equipm= ents and lateral join them to the 5 most recent events

Regards Martin

Am 08.03.2018 um 19:58 schrieb Stanton Schmidt:
Hi, 
I am new to the list so feel free to let me know if I am out of line.

My question is:
I have a table that has log events for pieces of equipment.  For each piece of equipment this table may conta= in 1 or more (hundreds potentially).
I need to write a query that will return only the last 5 log events for each (and every) piece of equipment.

log_table (
equipment_id character(30),
log_date date,
log_time time,
event_desc text
)

Thanks for your help.

stanton schmidt
= Database Administrator
direct. 920.471.4495  cell 920.660.1828

RGL
GO AHEAD. ASK&nb= sp;WHAT IF.
www.RGLlogistics.c= o m




--=_25fdee6d-d1a1-4060-a1b4-5941727bdaa9--