Re: Query question

Partition by is your friend...

https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group

On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt <sschmidt@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 from 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

where a.equipment_id = b.equipment_id

but all I end up with is 5 total records.

Stanton

---

From: "Martin Stöcker" <martin.stoecker@stb-datenservice.de>
To: "pgsql-sql" <pgsql-sql@lists.postgresql.org>
Sent: Thursday, March 8, 2018 1:07:30 PM
Subject: Re: Query question

My first 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 contain 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