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 1eu12R-0006Q2-K0 for pgsql-sql@arkaria.postgresql.org; Thu, 08 Mar 2018 19:17: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 1eu12Q-0008Jk-BD for pgsql-sql@arkaria.postgresql.org; Thu, 08 Mar 2018 19:17:14 +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 1eu12Q-0008Jb-1I for pgsql-sql@lists.postgresql.org; Thu, 08 Mar 2018 19:17:14 +0000 Received: from mx0a-0021dc01.pphosted.com ([148.163.148.220]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eu12M-0005Zq-8G for pgsql-sql@lists.postgresql.org; Thu, 08 Mar 2018 19:17:12 +0000 Received: from pps.filterd (m0096559.ppops.net [127.0.0.1]) by mx0a-0021dc01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w28J4DI9007660; Thu, 8 Mar 2018 13:17:09 -0600 Received: from mail.rglholdings.com (dns010.d.register.com [98.102.82.53] (may be forged)) by mx0a-0021dc01.pphosted.com with ESMTP id 2gfpma30x1-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Thu, 08 Mar 2018 13:17:08 -0600 Received: from mail.rglholdings.com (localhost [127.0.0.1]) by mail.rglholdings.com (Postfix) with ESMTPS id F3561234E44; Thu, 8 Mar 2018 13:17:07 -0600 (CST) Received: from localhost (localhost [127.0.0.1]) by mail.rglholdings.com (Postfix) with ESMTP id E52C9234CC6; Thu, 8 Mar 2018 13:17:07 -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 GipRwSns5tTB; Thu, 8 Mar 2018 13:17:07 -0600 (CST) Received: from mail.rglholdings.com (mail.ipa.rglholdings.com [10.1.20.35]) by mail.rglholdings.com (Postfix) with ESMTP id CACB314AF8; Thu, 8 Mar 2018 13:17:07 -0600 (CST) Date: Thu, 8 Mar 2018 13:17:07 -0600 (CST) From: Stanton Schmidt To: Martin =?utf-8?Q?St=C3=B6cker?= Cc: pgsql-sql Message-ID: <557870630.48523473.1520536627693.JavaMail.zimbra@rglholdings.com> In-Reply-To: References: <415803152.48510563.1520535505737.JavaMail.zimbra@rglholdings.com> Subject: Re: Query question MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_80c2c9b8-8cce-4849-abfc-a27986e9104a" 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: nDNjcMlIWdbvESydCU/YhnH7mJ9tBg== X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-03-08_10:,, signatures=0 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 priorityscore=1501 malwarescore=0 suspectscore=9 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-1803080213 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_80c2c9b8-8cce-4849-abfc-a27986e9104a Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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" =20 To: "pgsql-sql" =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 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 920.660.1828=20 RGL=20 GO AHEAD. ASK WHAT IF.=20 [ http://www.rgllogistics.com/ | www.RGLlogistics.co m ]=20 --=_80c2c9b8-8cce-4849-abfc-a27986e9104a Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
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 from log_table) a
, (select equipme= nt_id, log_date, log_time, event_desc from log_table order by log_date desc= , log_time desc limit 5) b
where a.equipment_id =3D b.equipment_i= d

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

Stanton
<= hr id=3D"zwchr" data-marker=3D"__DIVIDER__">
From: "Martin St=C3=B6cker" <martin.stoecker@stb-datenservice.d= e>
To: "pgsql-sql" <pgsql-sql@lists.postgresql.org>
<= b>Sent: Thursday, March 8, 2018 1:07:30 PM
Subject: Re: Query= question

My f= irst idea is to select all equipments 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 WHAT IF.
www.RGLlogistics.co m


--=_80c2c9b8-8cce-4849-abfc-a27986e9104a--