[SQL] Displaying first, last, count columns
I'm having a bit of a brain freeze and can't seem to come up with decent SQL for the following problem: I have a table "t" of the form "time_occurred TIMESTAMP, prog_data VARCHAR" and would like to create a query that outputs something of the form "first_seen, last_seen, count, prog_data". I have the current query which gets the first_seen and last_seen via subqueries, ala SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen, t3.count, t1.prog_data FROM t AS t1, t AS t2 WHERE t1.prog_data = t2.prog_data AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE prog_data = t1.prog_data) AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE prog_data = t1.prog_data) but I can't seem to work out how to get the count of all the records that have. I figure that this is probably a relatively common idiom ... can anyone suggest ways to go about doing this. Also, the performance of this is pretty horrible, but I figure that creating a column on t.prog_data should speed things up noticably, right? Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Displaying first, last, count columns
I would suggest:selectmax(time_occurred) AS last_seen,min(time_occurred) AS first_seen,count(*),prog_datafrom tgroup by prog_dataI would also suggest you use inner joins rather than put all your tables in the from and join in the where clause. It is much easier to read and understand what you are trying to do. The query you have is not exactly the same as what I put above but I bet the performance is bad because you have inner queries that have constraints based on the outer query. I usually avoid this as much as possible. -Aaron BonoOn 6/21/06, Worky Workerson <[EMAIL PROTECTED]> wrote: I'm having a bit of a brain freeze and can't seem to come up withdecent SQL for the following problem:I have a table "t" of the form "time_occurred TIMESTAMP, prog_dataVARCHAR" and would like to create a query that outputs something of the form "first_seen, last_seen, count, prog_data".I have the current query which gets the first_seen and last_seen viasubqueries, alaSELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen,t3.count, t1.prog_dataFROM t AS t1, t AS t2WHERE t1.prog_data = t2.prog_dataAND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHEREprog_data = t1.prog_data)AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHEREprog_data = t1.prog_data)but I can't seem to work out how to get the count of all the recordsthat have. I figure that this is probably a relatively common idiom ... can anyone suggest ways to go about doing this. Also, theperformance of this is pretty horrible, but I figure that creating acolumn on t.prog_data should speed things up noticably, right?Thanks!
Re: [SQL] Displaying first, last, count columns
At 10:55 AM 6/21/06, Worky Workerson wrote: I'm having a bit of a brain freeze and can't seem to come up with decent SQL for the following problem: I have a table "t" of the form "time_occurred TIMESTAMP, prog_data VARCHAR" and would like to create a query that outputs something of the form "first_seen, last_seen, count, prog_data". I have the current query which gets the first_seen and last_seen via subqueries, ala SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen, t3.count, t1.prog_data FROM t AS t1, t AS t2 WHERE t1.prog_data = t2.prog_data AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE prog_data = t1.prog_data) AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE prog_data = t1.prog_data) but I can't seem to work out how to get the count of all the records that have. I figure that this is probably a relatively common idiom ... can anyone suggest ways to go about doing this. Also, the performance of this is pretty horrible, but I figure that creating a column on t.prog_data should speed things up noticably, right? Is this what you're looking for? SELECT min(time_occurred) AS first_seen, max(time_occurred) AS last_seen, count(*), prog_data from t group by prog_data; Since this query has no WHERE or HAVING clause, this query will read the entire table. There is nothing you can do to speed it up. If you have enough RAM to hold the entire table (and appropriate setting to utilize it), then a second (and subsequent) run of the query will be faster than the first, but that's as good as it gets. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] How to get a result in one row
Hi! I'm using PostgreSQL 7.4.7. table_c id | nick +-- 1 | T 2 | S 3 | G 4 | A 5 | D ... table_m id | c --+ 22192 | 4 15041 | 3 21764 | 5 22192 | 1 15041 | 4 15041 | 2 ... where table_m.c is a foreign key on table_c.id SELECT table_m.id,table_c.nick FROM table_m AS m JOIN table_c AS c ON c.id=m.c WHERE m.id=22192 ORDER BY c.nick; returns: id | nick --+-- 22192 | A 22192 | T (2 rows) I'd like to get the result in only one row: id | nick --+-- 22192 | A,T (and similarly: 15041 | A,G,S ) As table_c can increase, I don't want to use the case construct. How can I do? Maybe writing a function. But how? TIA! virgi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] keeping last 30 entries of a log table
On Wed, 21 Jun 2006, Ben K. wrote:
Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;
insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30
update log set des='' where account_id=(select nextval('cy30'));
UPDATE 1
There are details to consider I guess. For example what if an update fails
and the sequence already advanced... Also, since we cycle the id, for
sorting, we'll need to add timestamp or something similar.
Only problem with this approach is that I need to be able to keep track of the
last 30 items per each account_id...so each account_id will have his last 30
messages in the table.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] How to get a result in one row
At 11:06 AM 6/21/06, [EMAIL PROTECTED] wrote: returns: id | nick --+-- 22192 | A 22192 | T (2 rows) I'd like to get the result in only one row: id | nick --+-- 22192 | A,T This question is in the archives (probably more than once). The answer is... Read the online docs about aggregate functions. There is an example that does (almost) exactly what you are asking. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to get a result in one row
> >I'd like to get the result in only one row: > >id | nick > >--+-- > >22192 | A,T > This question is in the archives (probably more than once). The answer > is...> > Read the online docs about aggregate functions. There is an example that > does (almost) exactly what you are asking. Where you referring to the tread regarding the LTREE contrib module for postgresql? http://archives.postgresql.org/pgsql-general/2006-06/msg00745.php I know I've seen this done using cursors in PL-PGSQL, but I would be interested if there was a solution with pre-existing aggregates. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to get a result in one row
At 02:24 PM 6/21/06, Richard Broersma Jr wrote: > >I'd like to get the result in only one row: > >id | nick > >--+-- > >22192 | A,T > This question is in the archives (probably more than once). The answer is...> > Read the online docs about aggregate functions. There is an example that > does (almost) exactly what you are asking. Where you referring to the tread regarding the LTREE contrib module for postgresql? http://archives.postgresql.org/pgsql-general/2006-06/msg00745.php I know I've seen this done using cursors in PL-PGSQL, but I would be interested if there was a solution with pre-existing aggregates. I was referring to threads like: http://archives.postgresql.org/pgsql-sql/2004-10/msg00124.php and threads on 9.Feb.2006 and 11.Mar.2006, which are on my system, but not on the above archive site. The various threads point to this page: http://www.postgresql.org/docs/8.1/interactive/xaggr.html Specifically the "array_accum" function on that page. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] join on next row
I am having brain freeze right now and was hoping someone could help me with a (fairly) simple query. I need to join on the next row in a similar table with specific criteria. I have a table with events per employee. I need to have a query that gives per employee each event and the event after it if it happened on the same day. The Events table structure is: EventID Employee EventDate EventTime EventType I want my query resultset to be Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2) Where Event(2) is the first event of the employee that took place after the other event. Example EventID EmployeeEventDate EventTime EventType 1 John6/15/2006 7:00A 2 Frank 6/15/2006 7:15B 3 Frank 6/15/2006 7:17C 4 John6/15/2006 7:20C 5 Frank 6/15/2006 7:25D 6 John6/16/2006 7:00A 7 John6/16/2006 8:30R Expected Results John, 6/15/2006, 7:00, A, 7:20, C Frank, 6/15/2006, 7:15, B, 7:17, C Frank, 6/15/2006, 7:17, C, 7:25, D John, 6/16/2006, 7:00, A, 8:30, R To get this result set it would have to be an inner join on employee and date where the second event time is greater then the first. But I don't want the all of the records with a greater time, just the first event after. Thank You Sim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Date ranges + DOW select question
Hi, I have a table which contains starttime, endtime and DOW; i.e. a weekly list of times for when a process must be started and ended. TABLE: cronTimes FIELDS: starttime, endtime, dayOfWeek I have another table which contains date ranges. TABLE: dateRanges FIELDS: dateStart, dateEnd I need to get a list of cronTimes records for a record in dateRanges and push these to a temporary table. i.e. tell me which dates are affected by cronTimes. I have got a little way on this but need some assistance. SELECT * FROM cronTimes WHERE starttime >= '00:00:00' AND endtime <= '23:59:59' AND dayOfWeek >= (EXTRACT(DOW FROM TIMESTAMP '2006-06-26')) AND dayOfWeek <= (EXTRACT(DOW FROM TIMESTAMP '2006-07-04')); The problem with the above is that by casting the dateStart and dateEnd they become numbers between 0 and 6 which inturn invalidates the < & > as they are nolonger working on dates, nor a sequence as numbers can be repeated. Do I need to generate a sequence of dates somehow so that each date in the range can be compared to the cronTimes table (so I can use the 'IN' condition)? Am I in the realms of plpgsql? Any advice on the above welcome. Joseppic. Send instant messages to your online friends http://uk.messenger.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Doubt in stored procedure
hi, iam a novice in postgresql. i want to insert a values to a table using stored procedure. plz mail me the sample codes and examples. thanks in advance with regards, Sathaa
Re: [SQL] keeping last 30 entries of a log table
I need to write a function which inserts a log entry in a log table and only
keeps the last 30 records. I was thinking of using a subquery similar to the
following:
insert into log (account_id, message) values (1, 'this is a test);
delete from log where account_id = 1 and id not in ( select id from log
where account_id = 1 order by timestamp desc limit 30);
I'm wondering if there is a more performance oriented method of doing the
delete that I'm not thinking of.
Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;
insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30
update log set des='' where account_id=(select nextval('cy30'));
UPDATE 1
There are details to consider I guess. For example what if an update fails
and the sequence already advanced... Also, since we cycle the id, for
sorting, we'll need to add timestamp or something similar.
My 2 pence...
P.S.
This A) failed me and I wonder if this is supposed to be so or if it's
just a place where no one treaded on ??
B) works fine except it doesn't advance the sequence.
A) update tc set des='b' where id=nextval('cy30')::int;
UPDATE 30
B) update tc set des='c' where id=currval('cy30');
UPDATE 1
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] join on next row
what about putting a limit of 1 on your select statement, so you will just get the first event greater then the initial date for the employee?On 6/18/06, Sim Zacks <[EMAIL PROTECTED]> wrote:I am having brain freeze right now and was hoping someone could help me with a (fairly) simple query.I need to join on the next row in a similar table with specific criteria.I have a table with events per employee.I need to have a query that gives per employee each event and the event after it if it happened on the same day.The Events table structure is:EventIDEmployeeEventDateEventTimeEventTypeI want my query resultset to beEmployee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2) Where Event(2) is the first event of the employee that took place afterthe other event.ExampleEventID EmployeeEventDate EventTime EventType1 John6/15/2006 7:00A 2 Frank 6/15/2006 7:15B3 Frank 6/15/2006 7:17C4 John6/15/2006 7:20C5 Frank 6/15/2006 7:25D 6 John6/16/2006 7:00A7 John6/16/2006 8:30RExpected ResultsJohn, 6/15/2006, 7:00, A, 7:20, CFrank, 6/15/2006, 7:15, B, 7:17, C Frank, 6/15/2006, 7:17, C, 7:25, DJohn, 6/16/2006, 7:00, A, 8:30, RTo get this result set it would have to be an inner join on employee anddate where the second event time is greater then the first. But I don't want the all of the records with a greater time, just the first event after.Thank YouSim---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq-- - Justin
