Обсуждение: Getting top 2 by Category

Поиск

Список

Период

Сортировка

Getting top 2 by Category

От

"Ozer, Pam"

Дата:

11 января 2011 г., 15:17:57

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: Getting top 2 by Category

От

"Ozer, Pam"

Дата:

11 января 2011 г., 15:53:17

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:

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

Re: Getting top 2 by Category

От

Peter Steinheuser

Дата:

11 января 2011 г., 16:00:19

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

Re: Getting top 2 by Category

От

msi77

Дата:

11 января 2011 г., 16:08:17

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>

Re: Getting top 2 by Category

От

Carla

Дата:

19 января 2011 г., 16:11:15

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
------------+------------

          3 |          2
          3 |          8
          4 |         10
          4 |         11
(4 rows)

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

Re: Getting top 2 by Category

От

Peter Steinheuser

Дата:

19 января 2011 г., 17:00:07

There's probably several ways - not saying this is best/optimal.

SELECT
categoryid, magazineid
FROM
magazinecategory a
WHERE (
SELECT
    COUNT(*)
FROM
    magazinecategory
WHERE
    categoryid = a.categoryid
AND
    magazineid <= a.magazineid
) < 3
order by categoryid, magazineid;

On Wed, Jan 19, 2011 at 3:11 PM, Carla <cgourofino@hotmail.com> wrote:

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
------------+------------

          3 |          2
          3 |          8
          4 |         10
          4 |         11
(4 rows)

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

Function To Strip HTML

От

"Ozer, Pam"

Дата:

23 февраля 2011 г., 19:58:24

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

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[udf_StripHTML]

(@HTMLText VARCHAR(MAX))

RETURNS VARCHAR(MAX)

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

Re: Function To Strip HTML

От

"Ozer, Pam"

Дата:

23 февраля 2011 г., 20:17:59

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

Thanks

Pam

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[udf_StripHTML]

(@HTMLText VARCHAR(MAX))

RETURNS VARCHAR(MAX)

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Getting top 2 by Category