[SQL] Comparing sequential rows in a result
I'm relatively new to SQL, and am frequently running into the same problem, How do I compare different rows in a result? for example: If I have a table of events consisting of a time stamp and the event type: timestamp, event_type 12:00 a 12:10 b 12:20 a ... I'd like to be able to select all the 'a' type events and calculate the time since the previous 'a' event, to get: timestamp, event_type, time_since_last 12:00 a 0:20 12:20 a NULL What's the best way to to accomplish this? Thanks in advance, Murray
Re: [SQL] Comparing sequential rows in a result
Here's one solution:
create temp sequence tsec;
create temp table ttab as select nextval('tsec'), * from (select * from
events where event_type='a' order by timestamp desc) as troz;
select ttab.*, ttab2.timestamp-ttab.timestamp from ttab join ttab as ttab2
on ttab2.nextval = ttab.nextval+1;
This works, but seems a very messy way to accomplish somehting quite simple.
On Wed, Oct 29, 2008 at 11:01 AM, Murray Long <[EMAIL PROTECTED]> wrote:
> I'm relatively new to SQL, and am frequently running into the same problem,
> How do I compare different rows in a result?
>
> for example:
> If I have a table of events consisting of a time stamp and the event type:
>
> timestamp, event_type
> 12:00 a
> 12:10 b
> 12:20 a
> ...
>
> I'd like to be able to select all the 'a' type events and calculate the
> time since the previous 'a' event, to get:
> timestamp, event_type, time_since_last
> 12:00 a 0:20
> 12:20 a NULL
>
> What's the best way to to accomplish this?
>
>
> Thanks in advance,
> Murray
>
>
>
>
[SQL] simple SQL query
Hello: I have a couple of queries that are giving me headaches. They are actually very simple, but I do not understand why I am not getting the expected results. Maybe I need new glasses. Please be kind. The table definitions are below. The table TMP_INDEX_MEMBER contains 21057 rows. These rows contain 3167 distinct ISINs. ISIN is a type of unique security identifier. This query select * from security where securitytypekey NOT IN ( 5,27) and ISIN IN (select ISIN from tmp_index_member ) returns 3069 rows. This tells me that there are 3069 ISINs in the SECURITY table. ISINs that I already know about. update tmp_index_member set securitykey = security.securitykey from security where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = security.ISIN results in Query returned successfully: 20545 rows affected, 2169 ms execution time. There are now 512 row in TMP_INDEX_MEMBER that have not been updated. OK now the confusion begins. I would expect the following query to return 512 rows. It returns zero. select * from tmp_index_member tim where tim.ISIN NOT IN (select distinct sec.ISIN from security sec where securitytypekey NOT IN ( 5,27) ) I want to add to SECURITY the securities that are new to me. To do this I need the above query to work. Question: does a UNIQUE constraint create an index? Maybe your fresh eyes will see something obvious. Many thanks to taking a look at this issue. KD SECURITY - contains the list my in-house security list CREATE TABLE "security" ( securitykey serial NOT NULL, securitytypekey integer, securitydesc character varying(125), bbcode character(25), ric character(15), sedol character(15), cusip character(12), isin character(15), securityissuecurriso character varying(3), underlyingcusip character varying(15), ticker character(30), underlyingisin character varying(15), expirationdate date, strikeprice numeric(19,6), put_call character(1), multiplier integer, createdate timestamp without time zone DEFAULT now(), ccy1isocode character(3), ccy2isocode character(3), contractdate date, fwdrate numeric(15,8), contract character(25), contractsize integer, unitprice numeric(10,6), underlyingticker character(20), underlyingbloomberg character(20), couponrate numeric(15,8), maturitydate date, exchangekey integer, CONSTRAINT pk_security PRIMARY KEY (securitykey), CONSTRAINT fk_security_securitytype FOREIGN KEY (securitytypekey) REFERENCES securitytype (securitytypekey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT unq_security_cusip UNIQUE (cusip, securitytypekey, securityissuecurriso), CONSTRAINT unq_security_isin UNIQUE (isin, securitytypekey, securityissuecurriso) ) WITHOUT OIDS; ALTER TABLE "security" OWNER TO postgres; -- Index: security_bbcode -- DROP INDEX security_bbcode; CREATE INDEX security_bbcode ON "security" USING btree (bbcode, securitytypekey); -- Index: "security_sectype_isoCurr" -- DROP INDEX "security_sectype_isoCurr"; CREATE INDEX "security_sectype_isoCurr" ON "security" USING btree (securitytypekey, securityissuecurriso); TMP_INDEX_MEMBER - contains the members of indexes such as S&P 500 and Russell 1000 CREATE TABLE tmp_index_member ( tmp_index_member_key serial NOT NULL, index_key integer, taskrunkey integer, isin character(15), cusip character(12), sedol character(12), bbcode character(15), curr character(5), bbtype character(20), secweight numeric(19,6), securitykey integer, gics_sector integer, gics_sector_name character(75), gics_industry_group integer, gics_industry_group_name character(75), gics_industry integer, gics_industry_name character(75), bbdesc character(50), CONSTRAINT pk_tmp_index_member PRIMARY KEY (tmp_index_member_key), CONSTRAINT fk_tmpindexmember_index_ FOREIGN KEY (index_key) REFERENCES index_ (index_key) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_tmpindexmember_taskrun FOREIGN KEY (taskrunkey) REFERENCES taskrun (taskrunkey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE tmp_index_member OWNER TO postgres; Kevin Duffy WR Capital Management
Re: [SQL] simple SQL query
On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote: > Hello: > > > > I have a couple of queries that are giving me headaches. > > They are actually very simple, but I do not understand why > > I am not getting the expected results. Maybe I need new glasses. > > Please be kind. > > > > The table definitions are below. > > > > The table TMP_INDEX_MEMBER contains 21057 rows. > > These rows contain 3167 distinct ISINs. ISIN is a type of unique > security identifier. > > > > This query > > select * from security > >where securitytypekey NOT IN ( 5,27) and ISIN IN > > (select ISIN from tmp_index_member ) > > returns 3069 rows. This tells me that there are 3069 ISINs > > in the SECURITY table. ISINs that I already know about. > > > > > > update tmp_index_member set securitykey = security.securitykey > >from security > >where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = > security.ISIN > > results in Query returned successfully: 20545 rows affected, 2169 ms > execution time. > > > > There are now 512 row in TMP_INDEX_MEMBER that have not been updated. > > OK now the confusion begins. > > > > I would expect the following query to return 512 rows. It returns zero. > > select * from tmp_index_member tim > > where tim.ISIN NOT IN > > (select distinct sec.ISIN from security sec where securitytypekey NOT > IN ( 5,27) ) > > > > > > I want to add to SECURITY the securities that are new to me. To do this > I need the above > > query to work. I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like: ... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))... > Question: does a UNIQUE constraint create an index? Yes. > Maybe your fresh eyes will see something obvious. > > Many thanks to taking a look at this issue. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple SQL query
Gentlemen: Thanks so much for your assistance. This returns 512 rows. select * from tmp_index_member tim where tim.ISIN NOT IN (select ISIN from security sec where ISIN is NOT NULL and securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) ) Can someone explain why the NULL ISINs in Security is causing so much grief? I do not get it. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Joseph Krogh Sent: Wednesday, October 29, 2008 3:58 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] simple SQL query On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote: > Hello: > > > > I have a couple of queries that are giving me headaches. > > They are actually very simple, but I do not understand why > > I am not getting the expected results. Maybe I need new glasses. > > Please be kind. > > > > The table definitions are below. > > > > The table TMP_INDEX_MEMBER contains 21057 rows. > > These rows contain 3167 distinct ISINs. ISIN is a type of unique > security identifier. > > > > This query > > select * from security > >where securitytypekey NOT IN ( 5,27) and ISIN IN > > (select ISIN from tmp_index_member ) > > returns 3069 rows. This tells me that there are 3069 ISINs > > in the SECURITY table. ISINs that I already know about. > > > > > > update tmp_index_member set securitykey = security.securitykey > >from security > >where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = > security.ISIN > > results in Query returned successfully: 20545 rows affected, 2169 ms > execution time. > > > > There are now 512 row in TMP_INDEX_MEMBER that have not been updated. > > OK now the confusion begins. > > > > I would expect the following query to return 512 rows. It returns zero. > > select * from tmp_index_member tim > > where tim.ISIN NOT IN > > (select distinct sec.ISIN from security sec where securitytypekey NOT > IN ( 5,27) ) > > > > > > I want to add to SECURITY the securities that are new to me. To do this > I need the above > > query to work. I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like: ... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))... > Question: does a UNIQUE constraint create an index? Yes. > Maybe your fresh eyes will see something obvious. > > Many thanks to taking a look at this issue. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple SQL query
On Wednesday 29 October 2008 21:56:14 Kevin Duffy wrote: > > Gentlemen: > > Thanks so much for your assistance. > > This returns 512 rows. > select * from tmp_index_member tim > where tim.ISIN NOT IN > (select ISIN from security sec > where ISIN is NOT NULL and >securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) ) > > Can someone explain why the NULL ISINs in Security is causing > so much grief? I do not get it. Sure. BTW; I ment "IS NULL OR securitytypekey NOT IN (5,27)". Remember that "WHERE col NOT IN ()" doesn't match NULL-values for "col", so these will both return "false" for NULL-value of "col": WHERE col NOT IN (2,3) WHERE col = 2 The reason is that NULL is "unknown", so testing against it also returns "unknown"(NULL). -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] trying to repair a bad header block
Hi all. I've seen this searching in google. After a select on a table, i got this: ERROR: invalid page header in block 399 of relation "xxx" I read about a tool called pg_filedump, and after some searchs about its usage, i execute pg_filedump $PG_DATA/base/xx/1234 (1234 is the oid of table xxx) As expected, i found the "Invalid header information" in block 399. Lots of this things inside: Block 399 - Block Offset: 0x0031e000 Offsets: Lower1663 (0x067f) Block: Size0 Version 95Upper 0 (0x) LSN: logid 5714531 recoff 0x00e0 Special 60660 (0xecf4) Items: 410 Free Space: 4294965633 Length (including item array): 1660 Error: Invalid header information. -- Item 1 -- Length:0 Offset: 2600 (0x0a28) Flags: 0x00 Item 2 -- Length:0 Offset:0 (0x) Flags: 0x00 Item 3 -- Length:0 Offset:0 (0x) Flags: 0x00 Item 4 -- Length:0 Offset:0 (0x) Flags: 0x00 Item 5 -- Length: 32767 Offset: 32767 (0x7fff) Flags: USED Error: Item contents extend beyond block. BlockSize<8192> Bytes Read<8192> Item Start<65534>. This for several items. Im triyng to 'repair' those items in any way, so i can dump the database and analyze the hardware. There is a way to 'correct' or blank the values somehow? I guess im going to lose some data, iisnt... Any hints? Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
[EMAIL PROTECTED] writes: > There is a way to 'correct' or blank the values somehow? I guess im going > to lose some data, iisnt... If you can tolerate losing the data on that page, just zero out the entire 8K page. dd from /dev/zero is the usual tool. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
On Wed, Oct 29, 2008 at 4:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] writes: >> There is a way to 'correct' or blank the values somehow? I guess im going >> to lose some data, iisnt... > > If you can tolerate losing the data on that page, just zero out the > entire 8K page. dd from /dev/zero is the usual tool. Would zero_damaged_pages work here? I know it's a shotgun to kill a flea, but it's also easier and safer for a lot of folks than dding a page in their table. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Wed, Oct 29, 2008 at 4:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> If you can tolerate losing the data on that page, just zero out the >> entire 8K page. dd from /dev/zero is the usual tool. > Would zero_damaged_pages work here? I know it's a shotgun to kill a > flea, but it's also easier and safer for a lot of folks than dding a > page in their table. It would work, but if you have any *other* damaged pages you might lose more than you were expecting ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
On Wed, Oct 29, 2008 at 6:36 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: >> On Wed, Oct 29, 2008 at 4:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >>> If you can tolerate losing the data on that page, just zero out the >>> entire 8K page. dd from /dev/zero is the usual tool. > >> Would zero_damaged_pages work here? I know it's a shotgun to kill a >> flea, but it's also easier and safer for a lot of folks than dding a >> page in their table. > > It would work, but if you have any *other* damaged pages you might > lose more than you were expecting ... Agreed. OTOH, on slip of the fingers for a newbie with dd and the whole table is gone. I guess it's always a trade off. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
On Wed, Oct 29, 2008 at 7:24 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: Oh, and to reply to myself and the original poster, you need to figure out what's causing the pages to get damaged. IT's usually bad hardware, then a buggy driver, then a buggy kernel / OS that can cause it. Run lots of tests. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple SQL query
"Kevin Duffy" <[EMAIL PROTECTED]> writes: > Can someone explain why the NULL ISINs in Security is causing > so much grief? I do not get it. NULL generally is taken as "unknown" in SQL comparisons. So if you have any nulls in the output of the sub-select, what the upper select sees is a situation like where 42 NOT IN (1,2,3, ..., NULL, ...) Now, if it finds 42 in the subquery output, it can say definitively that the result of NOT IN is FALSE, because 42 clearly *is* in the output. However, if it doesn't find a match, then what does that NULL represent? It's unknown, and therefore whether it's equal to 42 is unknown, and so the result of the NOT IN is unknown. And WHERE treats an unknown result the same as FALSE, so you don't get an output row from the upper query. NOT IN is generally pretty evil and best avoided: the funny behavior with nulls makes it not only a trap for novices, but hard for the system to optimize. Consider recasting as NOT EXISTS instead. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
