Thread: Getting top 2 by Category
<div class="WordSection1"><p class="MsoNormal">This is probably very simple but I am drawing a blank. Do I need to createa cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . Theresults I need are at the bottom. Any help would be greatly appreciated:<p class="MsoNormal"> <p class="MsoNormal">CREATETABLE magazinecategory<p class="MsoNormal">(<p class="MsoNormal"> magazinecategoryid smallint NOTNULL ,<p class="MsoNormal"> magazineid smallint,<p class="MsoNormal"> categoryid smallint<p class="MsoNormal">);<p class="MsoNormal"> <pclass="MsoNormal">INSERT INTO magazinecategory(<p class="MsoNormal"> magazinecategoryid,magazineid, categoryid)<p class="MsoNormal"> VALUES (1, 2, 3);<p class="MsoNormal"> <p class="MsoNormal"> <pclass="MsoNormal">INSERT INTO magazinecategory(<p class="MsoNormal"> magazinecategoryid,magazineid, categoryid)<p class="MsoNormal"> VALUES (2, 8, 3);<p class="MsoNormal"> <p class="MsoNormal"> <pclass="MsoNormal">INSERT INTO magazinecategory(<p class="MsoNormal"> magazinecategoryid,magazineid, categoryid)<p class="MsoNormal"> VALUES (3 9, 3);<p class="MsoNormal"> <p class="MsoNormal"> <pclass="MsoNormal">INSERT INTO magazinecategory(<p class="MsoNormal"> magazinecategoryid,magazineid, categoryid)<p class="MsoNormal"> VALUES (4, 10, 4);<p class="MsoNormal"> <p class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">INSERT INTO magazinecategory(<p class="MsoNormal"> magazinecategoryid, magazineid, categoryid)<p class="MsoNormal"> VALUES (5, 11, 4);<p class="MsoNormal"> <pclass="MsoNormal">INSERT INTO magazinecategory(<p class="MsoNormal"> magazinecategoryid,magazineid, categoryid)<p class="MsoNormal"> VALUES (6, 12,4);<p class="MsoNormal"> <p class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">The results I want are<p class="MsoNormal">CategoryID MagazineID<pclass="MsoNormal">3 2<p class="MsoNormal">3 8<p class="MsoNormal">4 10<p class="MsoNormal">4 11<p class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"> <div id="content"><p class="MsoNormal"><b><span style="font-size:11.5pt;font-family:"Arial","sans-serif";color:black">PamOzer</span></b></div></div>
Perfect. Thank You. I knew there had to be something simple.
From: Peter Steinheuser [mailto:psteinheuser@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 <pozer@automotive.com> 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
psteinheuser@myyearbook.com
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)
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
psteinheuser@myyearbook.com
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" <pozer@automotive.com>:> This is probably very simple but I am drawing a blank. Do I need tocreate 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( magazinecategoryidsmallint 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>
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
------------+------------(4 rows)
3 | 2
3 | 8
4 | 10
4 | 11
How can I do it in PG 8.3?
--On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam <pozer@automotive.com> 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
psteinheuser@myyearbook.com
SELECT
categoryid, magazineid
FROM
magazinecategory a
WHERE (
SELECT
COUNT(*)
FROM
magazinecategory
WHERE
categoryid = a.categoryid
AND
magazineid <= a.magazineid
) < 3
order by categoryid, magazineid;
2011/1/11 Peter Steinheuser <psteinheuser@myyearbook.com>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
------------+------------(4 rows)
3 | 2
3 | 8
4 | 10
4 | 11
How can I do it in PG 8.3?
--On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam <pozer@automotive.com> 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
psteinheuser@myyearbook.com
--
Peter Steinheuser
psteinheuser@myyearbook.com
<div class="WordSection1"><p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">I have the following function that I used in MSSQL. I wouldlike to create the same function in PostgreSql. I think I am a bit confused on how to create and set variables inPostgreSql. Can someone give me a place to start to create something like this? </span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">Thanks</span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">Pam</span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><span style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">ANSI_NULLS</span> <span style="color:blue">ON</span></span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">GO</span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><spanstyle="font-size:10.0pt;font-family:"Courier New""> <span style="color:blue">QUOTED_IDENTIFIER</span><span style="color:blue">ON</span></span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">GO</span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">CREATE</span><span style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">FUNCTION</span> [dbo]<span style="color:gray">.</span>[udf_StripHTML]</span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:gray">(</span><span style="font-size:10.0pt;font-family:"Courier New"">@HTMLText<span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:fuchsia">MAX</span><spanstyle="color:gray">))</span></span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">RETURNS</span><span style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">VARCHAR</span><span style="color:gray">(</span><spanstyle="color:fuchsia">MAX</span><span style="color:gray">)</span></span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">AS</span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">BEGIN</span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">DECLARE</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Start <span style="color:blue">INT</span></span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">DECLARE</span><spanstyle="font-size:10.0pt;font-family:"Courier New""> @End <span style="color:blue">INT</span></span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">DECLARE</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Length <span style="color:blue">INT</span></span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><spanstyle="font-size:10.0pt;font-family:"Courier New""> @Start <span style="color:gray">=</span><span style="color:fuchsia">CHARINDEX</span><span style="color:gray">(</span><span style="color:red">'<'</span><spanstyle="color:gray">,</span>@HTMLText<span style="color:gray">)</span></span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><spanstyle="font-size:10.0pt;font-family:"Courier New""> @End <span style="color:gray">=</span><span style="color:fuchsia">CHARINDEX</span><span style="color:gray">(</span><span style="color:red">'>'</span><spanstyle="color:gray">,</span>@HTMLText<span style="color:gray">,</span><span style="color:fuchsia">CHARINDEX</span><spanstyle="color:gray">(</span><span style="color:red">'<'</span><span style="color:gray">,</span>@HTMLText<spanstyle="color:gray">))</span></span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Length <span style="color:gray">=</span><span style="color:blue"> </span><spanstyle="color:gray">(</span>@End <span style="color:gray">-</span> @Start<span style="color:gray">)</span> <spanstyle="color:gray">+</span> 1</span><p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">WHILE</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Start <span style="color:gray">></span> 0</span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray">AND</span><spanstyle="font-size:10.0pt;font-family:"Courier New""> @End <span style="color:gray">></span>0</span><p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:gray">AND</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Length <span style="color:gray">></span> 0</span><p class="MsoNormal"style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">BEGIN</span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">SET</span><span style="font-size:10.0pt;font-family:"CourierNew""> @HTMLText <span style="color:gray">=</span> <span style="color:fuchsia">STUFF</span><spanstyle="color:gray">(</span>@HTMLText<span style="color:gray">,</span>@Start<span style="color:gray">,</span>@Length<spanstyle="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span></span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">SET</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Start <span style="color:gray">=</span> <span style="color:fuchsia">CHARINDEX</span><spanstyle="color:gray">(</span><span style="color:red">'<'</span><span style="color:gray">,</span>@HTMLText<spanstyle="color:gray">)</span></span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><span style="font-size:10.0pt;font-family:"CourierNew""> @End <span style="color:gray">=</span> <span style="color:fuchsia">CHARINDEX</span><spanstyle="color:gray">(</span><span style="color:red">'>'</span><span style="color:gray">,</span>@HTMLText<spanstyle="color:gray">,</span><span style="color:fuchsia">CHARINDEX</span><span style="color:gray">(</span><spanstyle="color:red">'<'</span><span style="color:gray">,</span>@HTMLText<span style="color:gray">))</span></span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">SET</span><span style="font-size:10.0pt;font-family:"CourierNew""> @Length <span style="color:gray">=</span><span style="color:blue"> </span><spanstyle="color:gray">(</span>@End <span style="color:gray">-</span> @Start<span style="color:gray">)</span> <spanstyle="color:gray">+</span> 1</span><p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">END</span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">RETURN</span><span style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:fuchsia">LTRIM</span><span style="color:gray">(</span><spanstyle="color:fuchsia">RTRIM</span><span style="color:gray">(</span>@HTMLText<span style="color:gray">))</span></span><pclass="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">END</span><p class="MsoNormal" style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><p class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">GO</span><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span></div>
I found a way to do this using regular expressions. Found this on another website
CREATE OR REPLACE FUNCTION strip_tags(TEXT) RETURNS TEXT AS $$
2 SELECT regexp_replace(regexp_replace($1, E'(?x)<[^>]*?(\s alt \s* = \s* ([\'"]) ([^>]*?) \2) [^>]*? >', E'\3'), E'(?x)(< [^>]*? >)', '', 'g')
3$$ LANGUAGE SQL;
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ozer, Pam
Sent: Wednesday, February 23, 2011 3:41 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Function To Strip HTML
I have the following function that I used in MSSQL. I would like to create the same function in PostgreSql. I think I am a bit confused on how to create and set variables in PostgreSql. Can someone give me a place to start to create something like this?
Thanks
Pam
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
GO