Re: [SQL] "How do I ..." SQL question
PFC wrote: > Return only four rows beginning at second row: > > > > SELECT count(*) AS count, name, year FROM a > > GROUP BY name, year > > ORDER BY count DESC, name ASC > > LIMIT 4 OFFSET 1; > > > > count name year > > --- -- ------ > >3 joe2004 s,e,e > >2 bob2003 w,e > >2 kim2003 s,s > >2 sue2004 s,w > > > > Select only places visited included in LIMITed query: > > > Is this : > > SELECT DISTINCT place FROM a,( > SELECT count(*) AS count, name, year FROM a > GROUP BY name, year > ORDER BY count DESC, name ASC > LIMIT 4 OFFSET 1 > ) as foo WHERE name=foo.name AND year=foo.year > > Problem with this approach is that you'll have to run the query twice, > one to get the hitlist by user, one for the places... > > > > > SELECT DISTINCT place FROM a ; > > > > place > > --- > > south > > west > > east Thanks, this worked. As it happens, I am already creating a temporary table for the hitlist for other uses so that isn't a problem. -Bob ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ORDER BY case insensitive?
How do I get the rows sorted in a case insensitive way? I have some queries that basically fit the form: SELECT * FROM MyTable ORDER BY Name; When I view the results, all of the Name's that start with an upper case letter precede all of the Name's that start with a lower case letter. I want them all in alphabetic order, regardless of case. I've looked in PostgreSQL Introduction and Concepts by Bruce Momjian and in the FAQ at http://postgresql.overtone.org/docs/faq-english.html Thanks for your help. -- Bob Swerdlow Chief Operating Officer Transpose, LLC [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Simple Query HELP!!!
On 22 Sep 2001 19:18:10 -0700, [EMAIL PROTECTED] (Paolo Colonnello) wrote: >Hello, I have the following, A table call People with 3 fields AGE >(Int) NAME (Txt) COMPANY (TxT) and I want to create a query than get >me a list with the seniors per company, for example : > >table PEOPLE > >NAME AGE COMPANY >Bob 33 Acme >Jane30 Acme >Bill20 Acme >Jose56 ATech >Siu 40 ATech >Paolo 28 IBM >Maria 38 IBM > >I need a query than will return a list with the seniors per company >like > >Bob >Jose >Maria > >Is there a way to do this with one query? > Do you care about ties? What if Ingrid, 38 yrs old, worked at IBM? Would you want to show both Ingrid and Maria? If so, this will work: Select Name From People t1 Inner Join (Select Company, Max(Age) As Oldest FROM People Group By Company) t2 ON t1.Company = t2.Company AND t1.Age = t2.Oldest HTH, Bob Barrows Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ORDER BY case insensitive?
Thanks for the suggestion, Jason. A co-worker of mine, however, had this response: Yes, but my guess is that that will first convert all million (or whatever) records to upper case, and then physically sort them. It won't be able to make use of the index. To make this efficient, do we need to uppercase all of the data before adding to the table? (yuk) - Bob - Original Message - From: "Jason Earl" <[EMAIL PROTECTED]> To: "Bob Swerdlow" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, October 04, 2001 3:47 PM Subject: Re: [SQL] ORDER BY case insensitive? > SELECT * FROM MyTable ORDER BY lower(Name); > > Should do the trick. > > Jason Earl > > --- Bob Swerdlow <[EMAIL PROTECTED]> wrote: > > How do I get the rows sorted in a case insensitive > > way? > > > > I have some queries that basically fit the form: > > SELECT * FROM MyTable ORDER BY Name; > > When I view the results, all of the Name's that > > start with an upper case > > letter precede all of the Name's that start with a > > lower case letter. I > > want them all in alphabetic order, regardless of > > case. > > > > I've looked in PostgreSQL Introduction and Concepts > > by Bruce Momjian and in > > the FAQ at > > http://postgresql.overtone.org/docs/faq-english.html > > > > Thanks for your help. > > > > -- > > Bob Swerdlow > > Chief Operating Officer > > Transpose, LLC > > [EMAIL PROTECTED] > > > > > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > __ > Do You Yahoo!? > NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. > http://geocities.yahoo.com/ps/info1 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL reference card
Hello, I'm looking for a reference card for SQL. I've searched quite a lot, but a simple card in PostScript/PDF seems to be hiding for me ;) Anyone?? Thanx in advance, Bob.. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] What benefits can I expect from schemas ?
Hi, I could not find much documentation about SQL 92 schemas that version 7.3 now supports. I understood it was a structure to group various objects, which allows faster privilege management, namespaces definition. Besides that, I don't see any advantages. I'd be glad if someone could point them out for me, or give doc links. Thanks. _ GRAND JEU SMS : Pour gagner un NOKIA 7650, envoyez le mot IF au 61321 (prix d'un SMS + 0.35 euro). Un SMS vous dira si vous avez gagné. Règlement : http://www.ifrance.com/_reloc/sign.sms ---(end of broadcast)--- TIP 3: 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
[SQL] Cross joining table with itself
I've been stumped trying to solve this problem via SQL. I have a table ID,
member1, member2, member3. In any row, any member field may have a value
or be null. I want to tally up how many times each member appears in the
table as a whole. For example, if two entries were ('a', '', 'c') and
('b', 'c', ''), I'd like my final result to be:
a, 1
b, 1
c, 2
Is this feasible? Thank you for any help.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with intervals
I'm getting an unexpected result using intervals in an expression:
select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
date
2003-10-26
(1 row)
When I get rid of the date cast it becomes clear what is happening:
select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
?column?
2003-10-26 23:00:00-08
(1 row)
I assumed '1 day' would always increment the date by 1, but it appears
that '1 day' just means '24 hours', and due to the daylight/standard
time shift, October 26 was 25 hours long this year.
Is this a Postgres bug, or is this correct SQL behavior? I'm running
Postgres 7.2.2.
Bob S.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problem with intervals
On Tuesday, Dec 2, 2003, at 03:53 US/Pacific, Alexander M. Pravking
wrote:
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
I'm getting an unexpected result using intervals in an expression:
select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
date
2003-10-26
(1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)
Aha! That solves my problem for now. I had also discovered that using
'25 hours'::interval works, but the integer approach is better.
It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.
Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".
Yet another reason to upgrade, I guess I'm gonna have to do it soon...
Thanks for your help!
Bob S.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to make a portable application?
I am sure that this is way too broad of a question to ask here, but heck, if anyone has any thoughts it would be helpful. I wonder, how do you make a truly portable sql application? You see apps out there that can run on just about any SQL server. I have always wondered how they can do that? I mean, I have run into so many problems just moving the simplest things from one system to the next, from Linux to Windows, from Mysql to Postgres, MSSQL to MySQL, whatever. The reason I ask is this, because when you go from Postgres to say Firebird, you have all your column or tables names in lowercase. Now that can probably just move right into Firebird if the datatypes are all setup right, but then all the names will be in UPPERCASE. Now your client app has a problem, wrong case. So how do they do it? Just enclose everything (all table and column names) in double-quotes? Do all SQL servers support that? FYI: My client app is Zope. Which, as I write this, is occurring to me that IT may be my problem. (Python code is case-sensitive) I am really thinking out loud here, so dont feel like you need to reply. But, having any thought from you more experienced developers is always helpful. Thanks.
[SQL] Query plan discrepancies
Hi, I have two databases that were created with identical schemas and
both filled in the exact same way, with the same indices etc., yet
they give different query paths for identical SELECTs. Normally this
wouldn't bug me, but one DB returns the select in relatively short
order, while the other one will hang for 15 minutes or so before I get
annoyed enough to kill it. Here's the kicker - yes, the table sizes in
the DBs is different, but the _larger_ database is the one that's
returning! This confuses me; thoughts?
- Bob
The statement:
select norm,count(norm) from medline_abstract_tokens where
pmid=7968456 and norm in (select norm_token from word_stats_base)
group by norm;
EXPLAIN from DB 1 (comes back):
QUERY PLAN
--
HashAggregate (cost=3282.48..3282.48 rows=1 width=8)
-> Nested Loop IN Join (cost=0.00..3282.35 rows=25 width=8)
-> Index Scan using medline_abstract_tokens_pmid on
medline_abstract_tokens (cost=0.00..6.67 rows=196 width=8)
Index Cond: (pmid = 7968456)
-> Index Scan using word_stats_base_norm on word_stats_base
(cost=0.00..3317.65 rows=1083 width=146)
Index Cond: (("outer".norm)::text =
(word_stats_base.norm_token)::text)
(6 rows)
EXPLAIN from DB 2 (doesn't come back):
QUERY PLAN
-
HashAggregate (cost=7763.55..7763.56 rows=1 width=8)
-> Nested Loop (cost=4363.86..7763.55 rows=1 width=8)
-> HashAggregate (cost=4363.86..4363.86 rows=200 width=146)
-> Seq Scan on word_stats_base (cost=0.00..4126.09
rows=95109 width=146)
-> Index Scan using medline_abstract_tokens_norm on
medline_abstract_tokens (cost=0.00..16.99 rows=1 width=8)
Index Cond: ((medline_abstract_tokens.norm)::text =
("outer".norm_token)::text)
Filter: (pmid = 7968456)
(7 rows)
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] getting count for a specific querry
On Apr 8, 2005 1:10 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote:> Thanks all.> I might have to add a button to do the count on command so they don't get> the hit.> I would want it to return the count of the condition, not the currently> displayed number of rows.>> Is there any other database engines that provide better performance?> (We just 2 moths moving to postgres and it is not live yet, but if I am> going to get results back slower then my 2 proc box running MSSQL in 2 gig> and 2 processor I cant see any reason to move to it)> The Postgres is on a 4 proc Dell with 8 gigs of memory.> I thought I could analyze our queries and our config to optimize.Judging postgresql on one single data point (count(*) performance) isquite unfair. Unless your system only operates on static data and isused to mostly do things like counting, in which case, why are you usinga database?PostgreSQL is a great fit for certain loads, and a poor fit for others.Are you going to have lots of people updating the database WHILE theselect count(*) queries are running? Are you going to be doing other,more interesting things than simply counting? If so, you really shouldbuild a test case that emulates what you're really going to be doingwith the system.I've found that the poor performance of aggregates in PostgreSQL isgenerally more than made up for by the outstanding behaviour it exhibitswhen under heavy parallel load.Note that the basic design of PostgreSQL's MVCC system is such thatwithout using some kind of trigger to maintain pre-calculated aggregateinformation, it will NEVER be as fast as most other databases at doingaggregates across large chunks of your data.---(end of broadcast)---TIP 7: don't forget to increase your free space map settingsFrom a simple/high level perspective why is this? That is why can't PostgreSQL do aggregates as well across large chunks of data. I'm assuming it extremely complicated. Otherwise the folks around here would have churned out a fix in a month or less and made this issue a past story.
Re: [SQL] getting count for a specific querry
On Apr 8, 2005 2:23 PM, Joel Fradkin <[EMAIL PROTECTED]> wrote: Believe me I just spent two months converting our app, I do not wish to giveup on that work. We do a great deal more then count. Specifically many ofour queries run much slower on postgres. As mentioned I purchased a 4 procbox with 8 gigs of memory for this upgrade (Dell may have been a poor choicebased on comments I have received). Even when I could see a query likeselect * from tblassoc where clientnum = 'WAZ' using indexed joins onlocation and jobtitle it is still taking 22 seconds to run compared to the 9seconds on MSSQL on a 2 proc 2 gig box. I got one of my querries to runfaster using a page cost of .2 but then the assoc query was running 50seconds, so I adjusted to a cost of 2 (tried 1.2, 2, 3, and 4 and did notsee hug changes in the assoc except it did not like .2).I have placed a call to commandprompt.com and am going to pay for somesupport to see if they have anything meaningful to add.It could be something with my hardware, my hardware config, my postgresconfig. I am just not sure. I know I have worked diligently to try to learnall I can and I used to think I was kinda smart.I set up the data on 4 10k scsi drives in a powervault and my wal on 2 15kdrives. I am using links to those from the install directory. It starts andstops ok this way, but maybe it should be different.I can tell you I am very happy to have this forum as I could not have gottento the point I am without the many usefull comments from folks on the list.I greatly appreciate everyone who has helped. But truth is if I cant get towork better then I have I may have to ditch the effort and bite the 70Kbullet. Its compounded by using 3 developers time for two months to yield ananswer that my boss may just fire me for. I figured since my first testshowed I could get data faster on the postgres box that I could with enoughstudy get all our data to go faster, but I am afraid I have not been verysuccessful.My failure is not a reflection postgres as you mentioned it is definatleygreat at some things. I have 90 some views not to mention as many storedprocedures that have been converted. I wrote an app to move the data and itworks great. But if it too slow I just will not be able to use forproduction.JoelJudging postgresql on one single data point (count(*) performance) isquite unfair. Unless your system only operates on static data and isused to mostly do things like counting, in which case, why are you usinga database?PostgreSQL is a great fit for certain loads, and a poor fit for others.Are you going to have lots of people updating the database WHILE theselect count(*) queries are running? Are you going to be doing other,more interesting things than simply counting? If so, you really shouldbuild a test case that emulates what you're really going to be doingwith the system.I've found that the poor performance of aggregates in PostgreSQL isgenerally more than made up for by the outstanding behaviour it exhibitswhen under heavy parallel load.Note that the basic design of PostgreSQL's MVCC system is such thatwithout using some kind of trigger to maintain pre-calculated aggregateinformation, it will NEVER be as fast as most other databases at doingaggregates across large chunks of your data.---(end of broadcast)---TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not matchHave you posted your postgresql config files for the folks here to review? I can't say I can help you with that because I too can only read the docs and go from there. But for specific situations you need specific configs. I would think you can get more out of postgresql with a some time and help from the people around here. Though count(*) looks like it may be slow.
Re: [SQL] getting count for a specific querry
On Apr 8, 2005 3:23 PM, Vivek Khera <[EMAIL PROTECTED]> wrote: On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote:> I set up the data on 4 10k scsi drives in a powervault and my wal on 2> 15k> drives. I am using links to those from the install directory. It> starts and> stops ok this way, but maybe it should be different.>Your problem might just be the choice of using a Dell RAID controller.I have a 1 year old box connected to a 14 disk powervault (PowerEdge2650) and it is dog slow compared to a dual opteron with 8 disks thatis replacing it. It is all I/O for me, and the dell's just are notknown for speedy I/O.Vivek Khera, Ph.D.+1-301-869-4449 x806 But that is relative I would think. Is the Dell RAID much faster than my desktop SATA drive with no RAID? I'm by any means as knowledgeable about I/O setup as many of you are but my 2 cents wonders if the Dell RAID is really that much slower than a competitively priced/speced alternative? Would Joel's problems just fade away if he wasn't using a Dell RAID?
Re: [SQL] getting count for a specific querry
On Apr 8, 2005 3:42 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Fri, 2005-04-08 at 15:35, Bob Henkel wrote:> On Apr 8, 2005 3:23 PM, Vivek Khera <[EMAIL PROTECTED]> wrote:> On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote:>> > I set up the data on 4 10k scsi drives in a powervault and> my wal on 2> > 15k> > drives. I am using links to those from the install> directory. It> > starts and> > stops ok this way, but maybe it should be different.> >>> Your problem might just be the choice of using a Dell RAID> controller.> I have a 1 year old box connected to a 14 disk powervault> (PowerEdge> 2650) and it is dog slow compared to a dual opteron with 8> disks that> is replacing it. It is all I/O for me, and the dell's just> are not> known for speedy I/O.>> Vivek Khera, Ph.D.> +1-301-869-4449 x806>>>> But that is relative I would think. Is the Dell RAID much faster than> my desktop SATA drive with no RAID? I'm by any means as knowledgeable> about I/O setup as many of you are but my 2 cents wonders if the Dell> RAID is really that much slower than a competitively priced/speced> alternative? Would Joel's problems just fade away if he wasn't using> a Dell RAID?My experience with the 3i controllers (See my earlier post) was that myold Pentium Pro200x2 machine with 512 meg ram and a generic Ultra WideSCSI card and a half dozen drives running software RAID 5 was faster.Seriously. So was my P-II-350 desktop with the same controller, and anolder Dual P-III-750 with only UltraSCSI running in a RAID-1 mirror set.The 3I is REALLY slow (or at least WAS slow) under linux. Interesting... Maybe Joel after a weekend of rest can try it on a different setup even if that different setup is just a power users development machine to see if he has same or worse timing results. Be wonderful if it magically sped up.
Re: [SQL] getting count for a specific querry
On Apr 9, 2005 10:00 AM, John DeSoi <[EMAIL PROTECTED]> wrote:On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote:> I don't think my clients would like me to aprox as it is a count of> their> records. What I plan on doing assuming I can get all my other problems> fixed> (as mentioned I am going to try and get paid help to see if I goofed> it up> some where) is make the count a button, so they don't wait everytime,> but> can choose to wait if need be, maybe I can store the last count with a> count> on day for the generic search it defaults to, and just have them do a> count> on demand if they have a specific query. Our screens have several> criteria> fields in each application.Here is an interface idea I'm working on for displaying query resultsin PostgreSQL. Maybe it will work for you if your connection methoddoes not prevent you from using cursors. I create a cursor an thenfetch the first 1000 rows. The status display has 4 paging buttons,something like this:|< < rows 1 - 1000 of ? > >|The user can hit the "next" button to get the next 1000. If less than1000 are fetched the ? is replaced with the actual count. They canpress the "last" button to move to the end of the cursor and get theactual count if they need it. So here the initial query should be fast,the user can get the count if they need it, and you don't have tore-query using limit and offset.The problem I'm looking into now (which I just posted on the generallist) is I don't see a way to get the table and column information froma cursor. If I fetch from a cursor, the table OID and column numbervalues are 0 in the row description. If I execute the same querydirectly without a cursor, the row description has the correct valuesfor table OID and column number.John DeSoi, Ph.D.http://pgedit.com/Power Tools for PostgreSQL Oracle Forms uses a similar method as you described and it works just fine. It will say Record 1 of ?(But I think the developer can set the amount of records cached so that if you set it to 10 and queried 5 records it would say record 1 of 5 because it would be under the cache amount.) Forms also offers a button that say get hit count. So if you really need to know the record count you can get it without moving off the current record.
[SQL] calculate time diffs across rows with single timestamp
First post - please pardon if I'm posted to the wrong group.
I have a table 'statuslog'
type varchar NOT NULL
id varchar NOT NULL
status varchar
datetime timestamp NOT NULL
Example data
type id status datetime
ASSET 001 AAA 2007-06-08 13:42:00.00
ASSET 002 AAA 2007-06-08 13:42:00.00
ASSET 003 AAA 2007-06-08 13:42:00.00
ASSET 001 BBB 2007-06-08 14:42:00.00
ASSET 001 CCC 2007-06-08 14:52:00.00
ASSET 002 BBB 2007-06-08 13:45:00.00
ASSET 001 DDD 2007-06-08 15:00:00.00
Consider this a log of transitional status changes. I now need to
sumarize time-in-status with grouping on type, id, status.
I can't currently modify the schema to include a second timestamp...
I'm not (yet) well versed in temp tables and cursors, but from what I
have researched and the suggestions from helpful coworkers, this seems
the way to go...?
Any suggestions on how I can build a result set that would return
ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
(The time diff can be seconds since epoch, some int, or whatever... in
testing I set up the schema using a second timestamp (the 'in' stamp of
the latter record by type/id became the 'out' stamp of the previous
record) and I simply subtracted the in from the out time in a sum() with
grouping.)
Thanks,
Bob
---(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] calculate time diffs across rows with single timestamp
Rodrigo De León wrote:
On Jun 13, 11:17 am, [EMAIL PROTECTED] (Bob Singleton) wrote:
Any suggestions on how I can build a result set that would return
ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
SELECT
TYPE, ID, STATUS
, (COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME) AS DURATION
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
---(end of broadcast)---
TIP 6: explain analyze is your friend
Awesome - thank you very much! Slightly modified to collapse by TYPE /
ID / STATUS
SELECT
TYPE, ID, STATUS, SUM(
(COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME))
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
Thanks for the lesson!
Bob Singleton
[SQL] Iterate and write a previous row to a temp table?
Revisiting a Time In Status query I received help on - I'm trying to narrow down a subset of data I return for analysis. Given a statusLog as entityId, statusId, timestamp that might look something like entityId | statusId | timestamp 001 | HLD | 2007-06-14 11:07:35.93 001 | RDY | 2007-06-15 11:07:35.93 001 | USE | 2007-06-16 11:07:35.93 001 | RDY | 2007-06-17 11:07:35.93 001 | MNT | 2007-06-18 11:07:35.93 I need to pull for a given span of time - say 2007-06-16 00:00:00.01 (let me call it startTime) to 2007-06-17 23:59:59.99 (call it endTime) in such a way that rows with a timestamp between startTime and endTime AND the latest record prior to or equal to startTime are returned. In the above simplified example, only the second and third rows would be returned. A colleague suggested a temp table, but I'm unsure how to iterate until I pass the startTime and then write the _previous_ and all subsequent rows to a temp table, stopping when I pass the endTime parameter. Any hints? Thanks! Bob Singleton ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Iterate and write a previous row to a temp table?
chester c young wrote: --- Bob Singleton <[EMAIL PROTECTED]> wrote: Revisiting a Time In Status query I received help on - I'm trying to narrow down a subset of data I return for analysis. Given a statusLog as entityId, statusId, timestamp that might look something like entityId | statusId | timestamp 001 | HLD | 2007-06-14 11:07:35.93 001 | RDY | 2007-06-15 11:07:35.93 001 | USE | 2007-06-16 11:07:35.93 001 | RDY | 2007-06-17 11:07:35.93 001 | MNT | 2007-06-18 11:07:35.93 I need to pull for a given span of time - say 2007-06-16 00:00:00.01 (let me call it startTime) to 2007-06-17 23:59:59.99 (call it endTime) in such a way that rows with a timestamp between startTime and endTime AND the latest record prior to or equal to startTime are returned. In the above simplified example, only the second and third rows would be returned. A colleague suggested a temp table, but I'm unsure how to iterate until I pass the startTime and then write the _previous_ and all subsequent rows to a temp table, stopping when I pass the endTime parameter. Any hints? Thanks! Bob Singleton couldn't you use the simple query: select * from sometable where timestamp between (select max(timestamp) from sometable where timestamp <= minTime) and maxTime This works very well unless I have no records where timestamp <= minTime. If I try select max(timestamp) from sometable where timestamp <= minTime; I get max - (1 row) And the blank row/line/value/? confuses the between call - I get 0 rows returned. (if minTime is later than at least one row's timestamp I will see something like) max 2007-06-08 17:42:00.18 (1 row) Any help greatly appreciated! bs
Re: [SQL] JOIN and aggregate problem
I might be missing something but does this solve your issue? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE); SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*) FROM t1 INNER JOIN t2 ON t1.d = t2.d AND t1.s = t2.s AND t1.c = t2.c WHERE t2.x = 'FALSE' GROUP BY t1.d, t1.s, t1.c, t2.x; On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurrence of T1: > D,S,C, COUNT > > COUNT is the number of matching D,S,C combinations in T2 where X = true. > There might be no matching pair in T2 or there might be match but X > is false. > > How can I express this? > > > > -- > > > Best Regards, > > Tarlika Elisabeth Schmitz > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
Scratch this one won't work for you. On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel wrote: > I might be missing something but does this solve your issue? > > CREATE TABLE t1(d INT,s INT, c INT); > > CREATE UNIQUE INDEX idx01_t1 > ON t1 USING btree (d, s, c); > > INSERT INTO t1 (d, s, c) > VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4); > > CREATE TABLE t2(d INT,s INT, c INT, x boolean); > > INSERT INTO t2(d, s, c, x) > VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE); > > SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*) > FROM t1 > INNER JOIN t2 >ON t1.d = t2.d > AND t1.s = t2.s > AND t1.c = t2.c > WHERE t2.x = 'FALSE' > GROUP BY t1.d, t1.s, t1.c, t2.x; > > On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz > wrote: >> I have 2 tables T1 and T2 >> >> T1 has the columns: D, S, C. The combination of D,S,C is unique. >> T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is >> not unique. >> >> I need to produce the following result for every occurrence of T1: >> D,S,C, COUNT >> >> COUNT is the number of matching D,S,C combinations in T2 where X = true. >> There might be no matching pair in T2 or there might be match but X >> is false. >> >> How can I express this? >> >> >> >> -- >> >> >> Best Regards, >> >> Tarlika Elisabeth Schmitz >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
How about this? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE),(5,5,5,TRUE),(5,5,5,TRUE); SELECT t1.d, t1.s, t1.c, CASE WHEN t2.x IS NULL THEN 0 ELSE COUNT(*) END FROM t1 LEFT OUTER JOIN t2 ON t1.d = t2.d AND t1.s = t2.s AND t1.c = t2.c AND t2.x = TRUE GROUP BY t1.d, t1.s, t1.c,t2.x; --DROP TABLE t1; --DROP TABLE t2; On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurrence of T1: > D,S,C, COUNT > > COUNT is the number of matching D,S,C combinations in T2 where X = true. > There might be no matching pair in T2 or there might be match but X > is false. > > How can I express this? > > > > -- > > > Best Regards, > > Tarlika Elisabeth Schmitz > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] subquery question
Does this help
Here is my test table data.
ID;DATE;VALUE
1;"2009-03-13";5
2;"2009-03-13";2
3;"2009-03-11";1
4;"2009-03-11";2
5;"2009-03-11";3
SELECT mydate AS day,
SUM(CASE WHEN id % 2 = 1 THEN value END) AS sum_odd,
SUM(CASE WHEN id % 2 = 0 THEN value END) AS sum_even
FROM xyz
GROUP BY mydate;
DATE;SUM_ODD;SUM_EVEN
"2009-03-11";4;2
"2009-03-13";5;2
Check the plans generated to see if one query actually appears better
than another.
Bob
On Thu, Mar 12, 2009 at 9:06 PM, Sebastian Böhm wrote:
> Hi,
> I have a table: (date timestamp, id integer, value integer)
> What Iam trying to do is to get a result that looks like this:
> day sum_odd sum_even
> 2009-01-01 6565 78867
> 2009-01-02 876785 87667
>
> basically a need to combine these two queries into one:
> SELECT date_trunc('day',date) AS day, sum(value) AS sum_odd FROM
> xyz WHERE id % 2 = 1 GROUP BY date_trunc('day',date)
> SELECT date_trunc('day',date) AS day, sum(value) AS sum_even FROM
> xyz WHERE id % 2 = 0 GROUP BY date_trunc('day',date)
> I found various ways to do this via unions or joins, but none of them seem
> efficient, what is the best way to do that ?
>
> thank you very much
> Sebastian
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
