n Of Each Group using GROUP BY

n Of Each Group using GROUP BY

Post by Travi » Sat, 20 Sep 2003 17:28:34



I have found a couple examples using Google on this, but none of them seem
to work for what I need, or my newbness is just taking over.  They all use
TOP n salary or sales, I just need to select n number of rows from a table
using GROUP BY, Here is my test stuff:

CREATE TABLE [Table1] (
 [product] [varchar] (50),
 [parent] [varchar] (50)
) ON [PRIMARY];

INSERT INTO Table1 (product,parent) VALUES('orange','red');
INSERT INTO Table1 (product,parent) VALUES('apple','red');
INSERT INTO Table1 (product,parent) VALUES('banana','red');
INSERT INTO Table1 (product,parent) VALUES('carrot','green');
INSERT INTO Table1 (product,parent) VALUES('corn','green');
INSERT INTO Table1 (product,parent) VALUES('tomato','green');
INSERT INTO Table1 (product,parent) VALUES('minivan','blue');
INSERT INTO Table1 (product,parent) VALUES('sedan','blue');
INSERT INTO Table1 (product,parent) VALUES('truck','blue');
INSERT INTO Table1 (product,parent) VALUES('mini','blue');

OK, this statement groups them correctly:

SELECT parent, product
FROM Table1
GROUP BY parent, product

But how would I just SELECT the 2 rows from each group like this?

red orange
red apple
green carrot
green corn
blue minivan
blue sedan

Is this possible?

T

 
 
 

n Of Each Group using GROUP BY

Post by Alejandro Mes » Sat, 20 Sep 2003 17:33:09


Try,

select parent, product
from table1 a
where (select count(*) from table1 b where b.parent = a.parent and a.product >= b.product) < 3
order by parent, product

AMB

 
 
 

n Of Each Group using GROUP BY

Post by Jacco Schalkwij » Sat, 20 Sep 2003 17:47:48


Hi Travis,

Thanks for the scripts, they make answering your question a lot quicker and
easier.

SELECT product,parent FROM table1
WHERE product IN (SELECT TOP 2 product FROM table1 t1 WHERE t1.parent =
table1.parent)

Note that the results you get back look like what you want, but that they
are in fact random. If you want to be certain of which results you get back
you have to determine which top 2 you want to get back and specify and order
by clause in the subquery (that's the select statement between brackets).
For example to get the top 2 products for each parent in alphabetical order:
SELECT product,parent FROM table1
WHERE product IN (SELECT TOP 2 product FROM table1 t1 WHERE t1.parent =
table1.parent ORDER by t1.product)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:> I have found a couple examples using Google on this, but none of them seem
> to work for what I need, or my newbness is just taking over.  They all use
> TOP n salary or sales, I just need to select n number of rows from a table
> using GROUP BY, Here is my test stuff:

> CREATE TABLE [Table1] (
>  [product] [varchar] (50),
>  [parent] [varchar] (50)
> ) ON [PRIMARY];

> INSERT INTO Table1 (product,parent) VALUES('orange','red');
> INSERT INTO Table1 (product,parent) VALUES('apple','red');
> INSERT INTO Table1 (product,parent) VALUES('banana','red');
> INSERT INTO Table1 (product,parent) VALUES('carrot','green');
> INSERT INTO Table1 (product,parent) VALUES('corn','green');
> INSERT INTO Table1 (product,parent) VALUES('tomato','green');
> INSERT INTO Table1 (product,parent) VALUES('minivan','blue');
> INSERT INTO Table1 (product,parent) VALUES('sedan','blue');
> INSERT INTO Table1 (product,parent) VALUES('truck','blue');
> INSERT INTO Table1 (product,parent) VALUES('mini','blue');

> OK, this statement groups them correctly:

> SELECT parent, product
> FROM Table1
> GROUP BY parent, product

> But how would I just SELECT the 2 rows from each group like this?

> red orange
> red apple
> green carrot
> green corn
> blue minivan
> blue sedan

> Is this possible?

> T

 
 
 

n Of Each Group using GROUP BY

Post by Chris Taylo » Sat, 20 Sep 2003 17:54:55


Hi,

This should do the trick for you

SELECT parent, product
  FROM Table1 t1
 GROUP BY parent, product
HAVING (SELECT count(*)
          FROM Table1
         WHERE parent = t1.parent and product < t1.product) < 2

Hope this helps

Chris Taylor

Quote:> I have found a couple examples using Google on this, but none of them seem
> to work for what I need, or my newbness is just taking over.  They all use
> TOP n salary or sales, I just need to select n number of rows from a table
> using GROUP BY, Here is my test stuff:

> CREATE TABLE [Table1] (
>  [product] [varchar] (50),
>  [parent] [varchar] (50)
> ) ON [PRIMARY];

> INSERT INTO Table1 (product,parent) VALUES('orange','red');
> INSERT INTO Table1 (product,parent) VALUES('apple','red');
> INSERT INTO Table1 (product,parent) VALUES('banana','red');
> INSERT INTO Table1 (product,parent) VALUES('carrot','green');
> INSERT INTO Table1 (product,parent) VALUES('corn','green');
> INSERT INTO Table1 (product,parent) VALUES('tomato','green');
> INSERT INTO Table1 (product,parent) VALUES('minivan','blue');
> INSERT INTO Table1 (product,parent) VALUES('sedan','blue');
> INSERT INTO Table1 (product,parent) VALUES('truck','blue');
> INSERT INTO Table1 (product,parent) VALUES('mini','blue');

> OK, this statement groups them correctly:

> SELECT parent, product
> FROM Table1
> GROUP BY parent, product

> But how would I just SELECT the 2 rows from each group like this?

> red orange
> red apple
> green carrot
> green corn
> blue minivan
> blue sedan

> Is this possible?

> T

 
 
 

n Of Each Group using GROUP BY

Post by Grouch » Sat, 20 Sep 2003 18:07:33



Quote:> I have found a couple examples using Google on this, but none of them
> seem to work for what I need, or my newbness is just taking over.

If you want to keep things simple and understandable
you can try the RAC utility for S2k.

-- Add an identity column to table1 for
-- sorting parent corresponding to your result.

CREATE TABLE table1 (rowid int identity,
 [product] [varchar] (50),
 [parent] [varchar] (50)
) ON [PRIMARY];

INSERT INTO table1 (product,parent) VALUES('orange','red');
INSERT INTO table1 (product,parent) VALUES('apple','red');
INSERT INTO table1 (product,parent) VALUES('banana','red');
INSERT INTO table1 (product,parent) VALUES('carrot','green');
INSERT INTO table1 (product,parent) VALUES('corn','green');
INSERT INTO table1 (product,parent) VALUES('tomato','green');
INSERT INTO table1 (product,parent) VALUES('minivan','blue');
INSERT INTO table1 (product,parent) VALUES('sedan','blue');
INSERT INTO table1 (product,parent) VALUES('truck','blue');
INSERT INTO table1 (product,parent) VALUES('mini','blue');





                  from rac
                    where pcnter<=2
                            order by rd'

parent product
--------- ----------
red      orange
red      apple
green  carrot
green  corn
blue     minivan
blue     sedan


www.rac4sql.net

 
 
 

n Of Each Group using GROUP BY

Post by Joe Celk » Sat, 20 Sep 2003 18:14:39


Thanks for the DDL, even if it was wrong.  You did not have a key, and
will all the columns being NULL-able, you could NEVER have a key.  No
key means no table.  The choice of datatypes was also screwed up and you
used proprietary square brackets -- you let ACCESS generate this without
cleaning it up for human beings, didn't you?

CREATE TABLE Foobar
(product CHAR (15) NOT NULL,
 parent CHAR (15) NOT NULL,
 PRIMARY KEY (product, parent));

Quote:>> How would I just SELECT the 2 rows from each group like this? <<

Try this instead.  You now have an idea of the range of the product
names and guaranteed only two in each parent group.  Don't make life
hard for yourself.

SELECT parent, MIN(product), MAX(product)
  FROM Foobar;

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

n Of Each Group using GROUP BY

Post by Travi » Sat, 20 Sep 2003 18:22:09


Thanx all!!   All great solutions.

T

 
 
 

n Of Each Group using GROUP BY

Post by Travi » Sat, 20 Sep 2003 19:06:17


CELKO-

Actually I used SQL2K enterprise manager to generate the scripts of a "test"
table I created rather quickly, then I "cleaned it up for human beings".  I
guess posting a conceptual DDL has a chance of stabbing me in the foot.  If
I posted the actual DDL I am working with (which contains a key column BTW),
it would seem overkill for my simple problem, but I digress.  And usenet
users would have alot more to nitpick about seeing my actual DDL...      see
i'm protecting myself here.  *wink*

Thanx for the suggestion and post.

T

Quote:> Thanks for the DDL, even if it was wrong.  You did not have a key, and
> will all the columns being NULL-able, you could NEVER have a key.  No
> key means no table.  The choice of datatypes was also screwed up and you
> used proprietary square brackets -- you let ACCESS generate this without
> cleaning it up for human beings, didn't you?

 
 
 

1. Rolling Up a Group (or, Grouping a Group)

Hi,

I'm trying to figure out how to Group a Group. Here's a sample of the raw data:

Member  CoverageCode  InsuredDate  InsuredAmount
------  ------------  -----------  -------------
BOB     A             01/01/1999   1000
ALICE   A             01/01/2000   1000
BOB     A             01/01/2001   1000
BOB     B             07/01/2001   1000

Now, let's say that in a Rate table, I have the following

CoverageCode  EffectiveFrom  EffectiveTo  Rate
------------  -------------  -----------  ----
A             01/01/1999     12/31/1999   0.10
A             01/01/2000     12/31/2000   0.15
A             01/01/2001     12/31/2001   0.20
B             01/01/2001     12/31/2001   0.22

My stored procedure calculates, sums, and groups by coverage code:

Member  TotalInsuredAmount
------  ------------------
BOB     300
BOB     220
ALICE   150

What I need to get as a result set from my stored procedure is the following:

Member  TotalInsuredAmount
------  ------------------
BOB     520
ALICE   150

I think it creates two "BOB" records because each one is grouped by a Coverage Code (Bob has 2 coverage "A" and 1 coverage "B")

What would be the SQL I could use to Group the Group, or roll-up the subtotals into just one grand total for each Member?

Thanks,

Scott

2. need new help for old query

3. Group by using datetime field - want to only group by date

4. Seeking wbtrv32.dll

5. Local Group or Global Group

6. 17296-LA-NEW ORLEANS-ORACLE-Windows NT-VMS-Client/Server-DBA Skills-DATABASE ADMINISTRATOR

7. grouping records , and updating the first record in each group

8. Colorado Career Opportunities

9. Help -- Grouping qry ---- Sub Grouping -Is this possible?

10. Returning top n results for each group with a Group By Statement

11. Copy permissions from user/group to different user/group

12. Count of groups via group by

13. Remapping/Copying SQL Logins from NT Local Groups to Domain Global Groups