[SQL] help needs in converting db2 function in postgresql.
Hi,
I need helping converting following db2 function in postgresql function.
Any pointer will be great help in proceeding me ahead.
CREATE FUNCTION in_liststring ( string CLOB(64K) )
RETURNS TABLE ( ordinal INTEGER, index INTEGER )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( VALUES ( 0, 0 )
UNION ALL
SELECT ordinal+1, COALESCE(NULLIF(
-- find the next delimiter ','
LOCATE(',', string, index+1), 0),
LENGTH(string)+1)
FROM t
-- to prevent a warning condition for infinite
-- recursions, we add the explicit upper
-- boundary for the "ordinal" values
WHERE ordinal < 1 AND
-- terminate if there are no further delimiters
-- remaining
LOCATE(',', string, index+1) <> 0 )
SELECT ordinal, index
FROM t
UNION ALL
-- add indicator for the end of the string
SELECT MAX(ordinal)+1, LENGTH(string)+1
FROM t
;
commit;
DROP FUNCTION INSTRTBL;
CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
RETURNS TABLE ( INSTRTBL CLOB(64K) )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( SELECT ordinal, index
FROM TABLE ( in_liststring(string) ) AS x )
SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
-- the join below makes sure that we have the lower and
-- upper index where we can find each of the ',' delimiters
-- that are separating the INSTRTBL. (For this, we exploit
-- the additional indexes pointing to the beginning and end
-- of the string.)
FROM t AS t1 JOIN t AS t2 ON
( t2.ordinal = t1.ordinal+1 )
;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Getting top 2 by Category
This is probably very simple but I am drawing a blank. Do I need to create a cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . The results I need are at the bottom. Any help would be greatly appreciated: CREATE TABLE magazinecategory ( magazinecategoryid smallint NOT NULL , magazineid smallint, categoryid smallint ); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (1, 2, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (2, 8, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (3 9, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (4, 10, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (5, 11, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (6, 12,4); The results I want are CategoryID MagazineID 3 2 3 8 4 10 4 11 Pam Ozer
Re: [SQL] Getting top 2 by Category
Perfect. Thank You. I knew there had to be something simple. From: Peter Steinheuser [mailto:psteinheu...@myyearbook.com] Sent: Tuesday, January 11, 2011 11:52 AM To: Ozer, Pam Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Getting top 2 by Category Well, if yoi have PG 8.4 and above - select categoryid, magazineid from ( select row_number() over (partition by categoryid order by categoryid,magazineid asc) as row_number, categoryid, magazineid from magazinecategory) foo where row_number < 3; categoryid | magazineid + 3 | 2 3 | 8 4 | 10 4 | 11 (4 rows) On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: This is probably very simple but I am drawing a blank. Do I need to create a cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . The results I need are at the bottom. Any help would be greatly appreciated: CREATE TABLE magazinecategory ( magazinecategoryid smallint NOT NULL , magazineid smallint, categoryid smallint ); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (1, 2, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (2, 8, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (3 9, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (4, 10, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (5, 11, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (6, 12,4); The results I want are CategoryID MagazineID 3 2 3 8 4 10 4 11 Pam Ozer -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] Getting top 2 by Category
Well, if yoi have PG 8.4 and above - select categoryid, magazineid from ( select row_number() over (partition by categoryid order by categoryid,magazineid asc) as row_number, categoryid, magazineid from magazinecategory) foo where row_number < 3; categoryid | magazineid + 3 | 2 3 | 8 4 | 10 4 | 11 (4 rows) On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: > This is probably very simple but I am drawing a blank. Do I need to create > a cursor to iterate through a table to grab the top 2 magazines per > category? Here is my table and some data . The results I need are at the > bottom. Any help would be greatly appreciated: > > > > CREATE TABLE magazinecategory > > ( > > magazinecategoryid smallint NOT NULL , > > magazineid smallint, > > categoryid smallint > > ); > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (1, 2, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (2, 8, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (3 9, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (4, 10, 4); > > > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (5, 11, 4); > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (6, 12,4); > > > > > > > > The results I want are > > CategoryID MagazineID > > 3 2 > > 3 8 > > 4 10 > > 4 11 > > > > > > > > *Pam Ozer* > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] Getting top 2 by Category
There some ways to do this in one query. Look here: http://www.sql-ex.ru/help/select16.php --- 11.01.11, 22:00, "Ozer, Pam" :> This is probably very simple but I am drawing a blank. Do I need to create a cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . The results I need are at the bottom. Any help would be greatly appreciated: CREATE TABLE magazinecategory( magazinecategoryid smallint NOT NULL , magazineid smallint, categoryid smallint); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (1, 2, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (2, 8, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (3 9, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (4, 10, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (5, 11, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (6, 12,4); The results I want areCategoryID MagazineID3 23 84 104 11 Pam Ozer> -- 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] help needs in converting db2 function in postgresql.
2011/1/11 Amar Dhole
> Hi,
> I need helping converting following db2 function in postgresql function.
> Any pointer will be great help in proceeding me ahead.
>
> CREATE FUNCTION in_liststring ( string CLOB(64K) )
> RETURNS TABLE ( ordinal INTEGER, index INTEGER )
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
> RETURN
> WITH t(ordinal, index) AS
> ( VALUES ( 0, 0 )
> UNION ALL
> SELECT ordinal+1, COALESCE(NULLIF(
> -- find the next delimiter ','
> LOCATE(',', string, index+1), 0),
> LENGTH(string)+1)
> FROM t
> -- to prevent a warning condition for infinite
> -- recursions, we add the explicit upper
> -- boundary for the "ordinal" values
> WHERE ordinal < 1 AND
> -- terminate if there are no further delimiters
> -- remaining
> LOCATE(',', string, index+1) <> 0 )
> SELECT ordinal, index
> FROM t
> UNION ALL
> -- add indicator for the end of the string
> SELECT MAX(ordinal)+1, LENGTH(string)+1
> FROM t
> ;
>
> commit;
>
> DROP FUNCTION INSTRTBL;
>
> CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
> RETURNS TABLE ( INSTRTBL CLOB(64K) )
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
> RETURN
> WITH t(ordinal, index) AS
> ( SELECT ordinal, index
> FROM TABLE ( in_liststring(string) ) AS x )
> SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
> -- the join below makes sure that we have the lower and
> -- upper index where we can find each of the ',' delimiters
> -- that are separating the INSTRTBL. (For this, we exploit
> -- the additional indexes pointing to the beginning and end
> -- of the string.)
> FROM t AS t1 JOIN t AS t2 ON
>( t2.ordinal = t1.ordinal+1 )
> ;
>
>
create or replace function instrtbl(text)
returns table(instrtbl text)
language sql
immutable
strict
as $$
SELECT * FROM regexp_split_to_table($1, ',')
$$;
fi...@filip=# select * from instrtbl( 'one, two, really long three' );
instrtbl
one
two
really long three
(3 rows)
I love PostgreSQL.
Filip
