agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

Query Question
15+ messages / 12 participants
[nested] [flat]

* Query Question
@ 1999-06-16 15:01 Bob Kruger <bkruger@mindspring.com>
  1999-06-16 15:12 ` Re: [SQL] Query Question Tom Lane <tgl@sss.pgh.pa.us>
  1999-06-16 15:19 ` Re: [SQL] Query Question Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 2 replies; 15+ messages in thread

From: Bob Kruger @ 1999-06-16 15:01 UTC (permalink / raw)
  To: pgsql-sql


Here is a simple query question that has me stumped.

I have a database that contains the information on vehicle maintenance
costs.  When a repair is done to a vehicle, the tag number and the amount
of the repair is recorded.

table structure runs as follows:

id		serial
po		varchar(12)
veh_no		varchar(8)
cost		real
comments	varchar(30)

I would like to be able to do a query in which I can list all of the
vehicles and a totalization of the costs, e.g. list one vehicle number, and
the sum of costs for that vehicle.  Because there are numerous entries for
each vehicle I know that some part of the query will have to include
"distinct veh_no" so that only one output for each vehicle is listed.

I have figured out a way to do this with Perl, but it takes two selects,
and I would prefer to do this with just one.

Any hints or ideas?

Thanks in advance for any assistance.

Regards - Bob Kruger 




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [SQL] Query Question
  1999-06-16 15:01 Query Question Bob Kruger <bkruger@mindspring.com>
@ 1999-06-16 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 15+ messages in thread

From: Tom Lane @ 1999-06-16 15:12 UTC (permalink / raw)
  To: Bob Kruger <bkruger@mindspring.com>; +Cc: pgsql-sql

Bob Kruger <bkruger@mindspring.com> writes:
> I would like to be able to do a query in which I can list all of the
> vehicles and a totalization of the costs, e.g. list one vehicle number, and
> the sum of costs for that vehicle.

I think you want GROUP BY, eg

	select veh_no, sum(cost) from table group by veh_no;

sum() and other aggregates apply across a group, not across the whole
table, when you use GROUP BY.  Notice you do NOT use DISTINCT ...
GROUP BY takes care of that for you.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [SQL] Query Question
  1999-06-16 15:01 Query Question Bob Kruger <bkruger@mindspring.com>
@ 1999-06-16 15:19 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 15+ messages in thread

From: Herouth Maoz @ 1999-06-16 15:19 UTC (permalink / raw)
  To: Bob Kruger <bkruger@mindspring.com>; pgsql-sql

At 18:01 +0300 on 16/06/1999, Bob Kruger wrote:


>
> Here is a simple query question that has me stumped.
>
> I have a database that contains the information on vehicle maintenance
> costs.  When a repair is done to a vehicle, the tag number and the amount
> of the repair is recorded.
>
> table structure runs as follows:
>
> id		serial
> po		varchar(12)
> veh_no		varchar(8)
> cost		real
> comments	varchar(30)
>
> I would like to be able to do a query in which I can list all of the
> vehicles and a totalization of the costs, e.g. list one vehicle number, and
> the sum of costs for that vehicle.  Because there are numerous entries for
> each vehicle I know that some part of the query will have to include
> "distinct veh_no" so that only one output for each vehicle is listed.

Do you mean

SELECT veh_no, sum( cost )
FROM the_table
GROUP BY veh_no;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Query question
@ 2005-04-20 16:11 Stéphane RIFF <stephane.riff@cerene.fr>
  2005-04-20 17:35 ` Re: Query question Franco Bruno Borghesi <franco@akyasociados.com.ar>
  0 siblings, 1 reply; 15+ messages in thread

From: Stéphane RIFF @ 2005-04-20 16:11 UTC (permalink / raw)
  To: pgsql-sql

Hi ,

I have table that represent a switch activity like this :

|         date                    | state  |
| 2005-04-20 17:00:00 |   0     |
| 2005-04-20 17:00:15 |   0     |
| 2005-04-20 17:00:30 |   1     |
| 2005-04-20 17:00:45 |   1     |
| 2005-04-20 17:01:00 |   1     |
| 2005-04-20 17:01:15 |   0     |
| 2005-04-20 17:01:30 |   0     |
| 2005-04-20 17:01:45 |   0     |

I want to get the date of each states change but i not a sql expert.
Can someone advices me

Thanks




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2005-04-20 16:11 Query question Stéphane RIFF <stephane.riff@cerene.fr>
@ 2005-04-20 17:35 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
  2005-04-21 09:06   ` Re: Query question Stéphane RIFF <stephane.riff@cerene.fr>
  0 siblings, 1 reply; 15+ messages in thread

From: Franco Bruno Borghesi @ 2005-04-20 17:35 UTC (permalink / raw)
  To: Stéphane RIFF <stephane.riff@cerene.fr>; +Cc: pgsql-sql

If you have a row every 15 seconds, the answer is quite easy:

SELECT
	A1.date
FROM
	activity A1
	LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval)
WHERE
	A1.state<>A2.state OR A2.state IS NULL
ORDER BY 1



Now if you don't have a row every 15 seconds, the answer is a bit more 
complex (at least I couldn't think of an easier solution):

SELECT
	min(TMP2.new_date)
FROM
	(
		SELECT
			DISTINCT
			TMP.new_date,
			max(TMP.old_date) AS max_old_date
		FROM
			(
				SELECT	
					A1.id AS new_id, A1.date AS new_date, A1.state AS new_state,
					A2.id AS old_id, A2.date AS old_date, A2.state AS old_state
				FROM
					activity A1
					LEFT JOIN activity A2 ON (A2.date<A1.date)
				ORDER BY
					A1.date, A2.date DESC	
			) AS TMP
		WHERE
			TMP.old_state<>TMP.new_state OR TMP.old_state IS NULL
		GROUP BY
			TMP.new_date
	) TMP2
GROUP BY
	TMP2.max_old_date
ORDER BY 1


I've tested both queries on postgreSQL 8 with the data you provided, and 
they both work. Anyway try them with larger datasets before using them 
in real life ;-)

Hope it helps.


Stéphane RIFF wrote:

> Hi ,
>
> I have table that represent a switch activity like this :
>
> |         date                    | state  |
> | 2005-04-20 17:00:00 |   0     |
> | 2005-04-20 17:00:15 |   0     |
> | 2005-04-20 17:00:30 |   1     |
> | 2005-04-20 17:00:45 |   1     |
> | 2005-04-20 17:01:00 |   1     |
> | 2005-04-20 17:01:15 |   0     |
> | 2005-04-20 17:01:30 |   0     |
> | 2005-04-20 17:01:45 |   0     |
>
> I want to get the date of each states change but i not a sql expert.
> Can someone advices me
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2005-04-20 16:11 Query question Stéphane RIFF <stephane.riff@cerene.fr>
  2005-04-20 17:35 ` Re: Query question Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2005-04-21 09:06   ` Stéphane RIFF <stephane.riff@cerene.fr>
  0 siblings, 0 replies; 15+ messages in thread

From: Stéphane RIFF @ 2005-04-21 09:06 UTC (permalink / raw)
  To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: pgsql-sql

I do some tests with your first query and it seems to works.
Thanks a lot for your answer, i will post the final thought later
Thanks again
bye

Franco Bruno Borghesi wrote:

> If you have a row every 15 seconds, the answer is quite easy:
>
>SELECT
>	A1.date
>FROM
>	activity A1
>	LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval)
>WHERE
>	A1.state<>A2.state OR A2.state IS NULL
>ORDER BY 1
>  
>
>
>
> Now if you don't have a row every 15 seconds, the answer is a bit more 
> complex (at least I couldn't think of an easier solution):
>
>SELECT
>	min(TMP2.new_date)
>FROM
>	(
>		SELECT
>			DISTINCT
>			TMP.new_date,
>			max(TMP.old_date) AS max_old_date
>		FROM
>			(
>				SELECT	
>					A1.id AS new_id, A1.date AS new_date, A1.state AS new_state,
>					A2.id AS old_id, A2.date AS old_date, A2.state AS old_state
>				FROM
>					activity A1
>					LEFT JOIN activity A2 ON (A2.date<A1.date)
>				ORDER BY
>					A1.date, A2.date DESC	
>			) AS TMP
>		WHERE
>			TMP.old_state<>TMP.new_state OR TMP.old_state IS NULL
>		GROUP BY
>			TMP.new_date
>	) TMP2
>GROUP BY
>	TMP2.max_old_date
>ORDER BY 1
>  
>
>
> I've tested both queries on postgreSQL 8 with the data you provided, 
> and they both work. Anyway try them with larger datasets before using 
> them in real life ;-)
>
> Hope it helps.
>
>
> Stéphane RIFF wrote:
>
>> Hi ,
>>
>> I have table that represent a switch activity like this :
>>
>> |         date                    | state  |
>> | 2005-04-20 17:00:00 |   0     |
>> | 2005-04-20 17:00:15 |   0     |
>> | 2005-04-20 17:00:30 |   1     |
>> | 2005-04-20 17:00:45 |   1     |
>> | 2005-04-20 17:01:00 |   1     |
>> | 2005-04-20 17:01:15 |   0     |
>> | 2005-04-20 17:01:30 |   0     |
>> | 2005-04-20 17:01:45 |   0     |
>>
>> I want to get the date of each states change but i not a sql expert.
>> Can someone advices me
>>
>> Thanks
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.10.1 - Release Date: 20/04/2005
>  
>




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Query question
@ 2012-01-26 12:00 John Tuliao <jptuliao@htechcorp.net>
  2012-01-27 19:04 ` Re: Query question Lew <noone@lewscanon.com>
  0 siblings, 1 reply; 15+ messages in thread

From: John Tuliao @ 2012-01-26 12:00 UTC (permalink / raw)
  To: pgsql-sql

I seem to have a problem with a specific query:

The inside query seems to work on it's own:

             select prefix
             from john_prefix
             where strpos(jpt_test.number,john_prefix.prefix) = '1'
             order by char_length(john_prefix.prefix) desc limit 1

but when I execute it with this:

UPDATE
     jpt_test
set
     number = substring(number from length(john_prefix.prefix)+1)
from
     john_prefix
where
     prefix in (
             select prefix
             from john_prefix
             where strpos(jpt_test.number,john_prefix.prefix) = '1'
             order by char_length(john_prefix.prefix) desc limit 1
     ) ;

table contents are as follows

john_prefix table:

prefix
---------
123
234

jpt_test table:

number
-----------
1237999999
0234999999 <<< supposed to have no match
2349999999

Am I missing something here? Any help will be appreciated.

Regards,
JPT




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2012-01-26 12:00 Query question John Tuliao <jptuliao@htechcorp.net>
@ 2012-01-27 19:04 ` Lew <noone@lewscanon.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Lew @ 2012-01-27 19:04 UTC (permalink / raw)
  To: pgsql-sql

On 01/26/2012 04:00 AM, John Tuliao wrote:
> I seem to have a problem with a specific query:
>
> The inside query seems to work on it's own:
>
> select prefix
> from john_prefix
> where strpos(jpt_test.number,john_prefix.prefix) = '1'
> order by char_length(john_prefix.prefix) desc limit 1
>
> but when I execute it with this:
>
> UPDATE
>     jpt_test
> set
>     number = substring(number from length(john_prefix.prefix)+1)
> from
>     john_prefix
> where
>     prefix in (
>         select prefix
>         from john_prefix
>         where strpos(jpt_test.number,john_prefix.prefix) = '1'
>         order by char_length(john_prefix.prefix) desc limit 1
>     ) ;
>
> table contents are as follows
>
> john_prefix table:
>
> prefix
> ---------
> 123
> 234
>
> jpt_test table:
>
> number
> -----------
> 1237999999
> 0234999999 <<< supposed to have no match
> 2349999999
>
> Am I missing something here? Any help will be appreciated.

I'm going to guess that it's because you didn't use a separate alias for the 
FROM in the correlated subquery.

Doesn't STRPOS() return INTEGER, not TEXT?

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Query question
@ 2018-03-08 18:58 Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:07 ` Re: Query question Martin Stöcker <martin.stoecker@stb-datenservice.de>
  2018-03-08 19:48 ` Re: Query question Thomas Kellerer <spam_eater@gmx.net>
  0 siblings, 2 replies; 15+ messages in thread

From: Stanton Schmidt @ 2018-03-08 18:58 UTC (permalink / raw)
  To: pgsql-sql@lists.postgresql.org

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. [ callto:920.884.1281 | 920. ] 471.4495 cell 920.660.1828 

RGL 
GO AHEAD. ASK WHAT IF. 
[ http://www.rgllogistics.com/ | www.RGLlogistics.co m ] 

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
@ 2018-03-08 19:07 ` Martin Stöcker <martin.stoecker@stb-datenservice.de>
  2018-03-08 19:17   ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  1 sibling, 1 reply; 15+ messages in thread

From: Martin Stöcker @ 2018-03-08 19:07 UTC (permalink / raw)
  To: pgsql-sql@lists.postgresql.org

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. <callto:920.884.1281>471.4495  cell 920.660.1828
>
> RGL
> GO AHEAD.ASKWHAT IF.
> www.RGLlogistics.co m <http://www.rgllogistics.com/;

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:07 ` Re: Query question Martin Stöcker <martin.stoecker@stb-datenservice.de>
@ 2018-03-08 19:17   ` Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:20     ` Re: Query question Jeff Fletcher <jeff.fletcher@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Stanton Schmidt @ 2018-03-08 19:17 UTC (permalink / raw)
  To: Martin Stöcker <martin.stoecker@stb-datenservice.de>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>

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. [ callto:920.884.1281 | 920. ] 471.4495 cell 920.660.1828 

RGL 
GO AHEAD. ASK WHAT IF. 
[ http://www.rgllogistics.com/ | www.RGLlogistics.co
                          m ] 

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:07 ` Re: Query question Martin Stöcker <martin.stoecker@stb-datenservice.de>
  2018-03-08 19:17   ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
@ 2018-03-08 19:20     ` Jeff Fletcher <jeff.fletcher@gmail.com>
  2018-03-08 19:28       ` Re: Query question David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Jeff Fletcher @ 2018-03-08 19:20 UTC (permalink / raw)
  To: Stanton Schmidt <sschmidt@rgllogistics.com>; +Cc: Martin Stöcker <martin.stoecker@stb-datenservice.de>; pgsql-sql <pgsql-sql@lists.postgresql.org>

Partition by is your friend...

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


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. <callto:920.884.1281>471.4495  cell 920.660.1828
> <(920)%20660-1828>
>
> RGL
> GO AHEAD. ASK WHAT IF.
> www.RGLlogistics.co m <http://www.rgllogistics.com/;
>
>
>
>

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:07 ` Re: Query question Martin Stöcker <martin.stoecker@stb-datenservice.de>
  2018-03-08 19:17   ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:20     ` Re: Query question Jeff Fletcher <jeff.fletcher@gmail.com>
@ 2018-03-08 19:28       ` David G. Johnston <david.g.johnston@gmail.com>
  2018-03-08 20:17         ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  0 siblings, 1 reply; 15+ messages in thread

From: David G. Johnston @ 2018-03-08 19:28 UTC (permalink / raw)
  To: Jeff Fletcher <jeff.fletcher@gmail.com>; +Cc: Stanton Schmidt <sschmidt@rgllogistics.com>; Martin Stöcker <martin.stoecker@stb-datenservice.de>; pgsql-sql <pgsql-sql@lists.postgresql.org>

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

<not tested, syntax is close but might have errors>
select *
from (select distinct equipment_id from log_table) a,
lateral get_log_data_for_equipment(equipment_id, 5)

then write the get_log_data_for_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 select
distinct with select should be considered.  The presence of "distinct"
(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>
wrote:

> 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. <callto:920.884.1281>471.4495  cell 920.660.1828
>> <(920)%20660-1828>
>>
>> RGL
>> GO AHEAD. ASK WHAT IF.
>> www.RGLlogistics.co m <http://www.rgllogistics.com/;
>>
>>
>>
>>
>

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:07 ` Re: Query question Martin Stöcker <martin.stoecker@stb-datenservice.de>
  2018-03-08 19:17   ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
  2018-03-08 19:20     ` Re: Query question Jeff Fletcher <jeff.fletcher@gmail.com>
  2018-03-08 19:28       ` Re: Query question David G. Johnston <david.g.johnston@gmail.com>
@ 2018-03-08 20:17         ` Stanton Schmidt <sschmidt@rgllogistics.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Stanton Schmidt @ 2018-03-08 20:17 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Jeff Fletcher <jeff.fletcher@gmail.com>; Martin Stöcker <martin.stoecker@stb-datenservice.de>; pgsql-sql <pgsql-sql@lists.postgresql.org>

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 partitioning worked Thanks. 

stanton schmidt 
Database Administrator 
direct. [ callto:920.884.1281 | 920. ] 471.4495 cell 920.660.1828 

RGL 
GO AHEAD. ASK WHAT IF. 
[ http://www.rgllogistics.com/ | www.RGLlogistics.co m ] 


From: "David G. Johnston" <david.g.johnston@gmail.com> 
To: "Jeff Fletcher" <jeff.fletcher@gmail.com> 
Cc: "Stanton Schmidt" <sschmidt@rgllogistics.com>, "Martin Stöcker" <martin.stoecker@stb-datenservice.de>, "pgsql-sql" <pgsql-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 with the flow here... 

<not tested, syntax is close but might have errors> 
select * 
from (select distinct equipment_id from log_table) a, 
lateral get_log_data_for_equipment(equipment_id, 5) 

then write the get_log_data_for_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 select distinct with select should be considered. The presence of "distinct" (without 'on') is a code smell for me. 

David J. 


On Thu, Mar 8, 2018 at 12:20 PM, Jeff Fletcher < [ mailto:jeff.fletcher@gmail.com | jeff.fletcher@gmail.com ] > wrote: 



Partition by is your friend... 

[ https://urldefense.proofpoint.com/v2/url?u=https-3A__stackoverflow.com_questions_1124603_grouped-2Dl... | https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-ea... [stackoverflow.com] ] 


On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt < [ mailto:sschmidt@rgllogistics.com | sschmidt@rgllogistics.com ] > wrote: 

BQ_BEGIN

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" < [ mailto:martin.stoecker@stb-datenservice.de | martin.stoecker@stb-datenservice.de ] > 
To: "pgsql-sql" < [ mailto:pgsql-sql@lists.postgresql.org | 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: 

BQ_BEGIN

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. [ callto:920.884.1281 | 920. ] 471.4495 cell [ tel:(920)%20660-1828 | 920.660.1828 ] 

RGL 
GO AHEAD. ASK WHAT IF. 
[ http://www.rgllogistics.com/ | www.RGLlogistics.co
                          m ] 





BQ_END



BQ_END

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Query question
  2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
@ 2018-03-08 19:48 ` Thomas Kellerer <spam_eater@gmx.net>
  1 sibling, 0 replies; 15+ messages in thread

From: Thomas Kellerer @ 2018-03-08 19:48 UTC (permalink / raw)
  To: pgsql-sql

Stanton Schmidt schrieb am 08.03.2018 um 19:58:
> 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
> )

Queries like that are typically solved using window functions:

     select *
     from (
       select equipment_id,
              log_date,
              log_time,
              event_desc,
              row_number() over (partition by equipment_id order by log_date desc, log_time desc) as rn
       from log_table
     ) t
     where rn <= 5;


Unrelated, but: why aren't you storing "log_date_time" in a single timestamp?







^ permalink  raw  reply  [nested|flat] 15+ messages in thread

end of thread, other threads:[~2018-03-08 20:17 UTC | newest]

Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-16 15:01 Query Question Bob Kruger <bkruger@mindspring.com>
1999-06-16 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-16 15:19 ` Herouth Maoz <herouth@oumail.openu.ac.il>
2005-04-20 16:11 Query question Stéphane RIFF <stephane.riff@cerene.fr>
2005-04-20 17:35 ` Re: Query question Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-04-21 09:06   ` Re: Query question Stéphane RIFF <stephane.riff@cerene.fr>
2012-01-26 12:00 Query question John Tuliao <jptuliao@htechcorp.net>
2012-01-27 19:04 ` Re: Query question Lew <noone@lewscanon.com>
2018-03-08 18:58 Query question Stanton Schmidt <sschmidt@rgllogistics.com>
2018-03-08 19:07 ` Re: Query question Martin Stöcker <martin.stoecker@stb-datenservice.de>
2018-03-08 19:17   ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
2018-03-08 19:20     ` Re: Query question Jeff Fletcher <jeff.fletcher@gmail.com>
2018-03-08 19:28       ` Re: Query question David G. Johnston <david.g.johnston@gmail.com>
2018-03-08 20:17         ` Re: Query question Stanton Schmidt <sschmidt@rgllogistics.com>
2018-03-08 19:48 ` Re: Query question Thomas Kellerer <spam_eater@gmx.net>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox