Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping

Author

Topic

pmw
Starting Member

6 Posts

Posted - 2004-05-14 : 07:41:55

I have a table that looks like the following

PRODUCTNO | NUMBER | PCOUNT

00000264-0000-0000-0000-000000000000 | - E | 2
00000013-0000-0000-0000-000000000000 | - E | 1
00000265-0000-0000-0000-000000000000 | - E | NULL
00000266-0000-0000-0000-000000000000 | - R | NULL
00000014-0000-0000-0000-000000000000 | - R | NULL
00000267-0000-0000-0000-000000000000 | - R | NULL
000017D1-0000-0000-0000-000000000000 | 007 | 9
000017D2-0000-0000-0000-000000000000 | 007 | 1
000017D3-0000-0000-0000-000000000000 | 007 | NULL
000017D4-0000-0000-0000-000000000000 | 008 | 1
000017D5-0000-0000-0000-000000000000 | 008 | 1
000017D6-0000-0000-0000-000000000000 | 008 | NULL
000007FF-0000-0000-0000-000000000000 | 107-R | 1
00000822-0000-0000-0000-000000000000 | 107-R | NULL
00000823-0000-0000-0000-000000000000 | 107-R | NULL

I need to select 1 PRODUCTNO & NUMBER for each NUMBER where the PCOUNT is the highest

This is what I've got so far but it doesn't give me the PRODUCTNO, which is what I really need.

SELECT NUMBER, MAX(PCOUNT) AS 'PCOUNT'
INTO #PROD_HIGH
FROM #PRODS
GROUP BY NUMBER

Any help would be appreciated

P

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-05-14 : 08:42:06

[code]
select
a.PRODUCTNO, a.number
from
yourTable as a
where a.pcount = (
select max(b.pcount)
from yourTable as b
where a.number = b.number
and pcount is not null)
order by a.number
[/code]

________________
Make love not war!

pmw
Starting Member

6 Posts

Posted - 2004-05-14 : 09:24:20

Thanks for that but it's not quite what I'm after.

It's returning multiple rows were the PCOUNT is the same. 008 for example returns 2 rows.

I also need it to return 1 row if the PCOUNT is null for all occurrences of a number.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-05-14 : 09:32:40

quote:

It's returning multiple rows were the PCOUNT is the same. 008 for example returns 2 rows.

Of course it returns two where the pcount is the same. You want the highest pcount but for code 008 you have two with pcount = 1, which do you return?!

quote:

I also need it to return 1 row if the PCOUNT is null for all occurrences of a number.

Your productno are unique so how do you know which one to return if they all have NULL pcounts?!

I don't understand this...

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

pmw
Starting Member

6 Posts

Posted - 2004-05-14 : 09:36:32

If the PCOUNT value are the same (numeric or NULL then I don't mind which one comes back as long as it's just one per number.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-05-14 : 10:48:11

[code]
select
max(a.PRODUCTNO), a.number
from
yourTable as a
where isnull(a.pcount, 'XXXXX') = (
select max(isnull(b.pcount, 'XXXXX'))
from yourTable as b
where a.number = b.number)
group by a.number
order by a.number
[/code]

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

pmw
Starting Member

6 Posts

Posted - 2004-05-14 : 10:58:10

Thanks for the reply and I can see what you're doing but I get the following when I run it

Server: Msg 409, Level 16, State 2, Line 1
The maximum aggregate operation cannot take a uniqueidentifier data type as an argument.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-14 : 11:02:04

I don't understand...

Isn't


SELECT PRODUCTNO, NUMBER, MAX(PCOUNT) AS 'PCOUNT'
INTO #PROD_HIGH
FROM #PRODS
GROUP BY PRODUCTNO, NUMBER

What you're after?

Brett

8-)

pmw
Starting Member

6 Posts

Posted - 2004-05-14 : 11:18:08

OK this is what I've got.

I've got duplicate products in mytable that have the same NUMBER but a unique PRODUCTNO.

I want to select 1 row for each number (preferable based on max(PCOUNT)

So for example if I've got

PRODUCTNO | NUMBER | PCOUNT

00000264-0000-0000-0000-000000000000 | - E | 2
00000013-0000-0000-0000-000000000000 | - E | 1
00000265-0000-0000-0000-000000000000 | - R | NULL
00000266-0000-0000-0000-000000000000 | - R | NULL
000017D4-0000-0000-0000-000000000000 | 008 | 1
000017D5-0000-0000-0000-000000000000 | 008 | 1

I want to return

00000264-0000-0000-0000-000000000000 | - E | 2
00000265-0000-0000-0000-000000000000 | - R | NULL
000017D4-0000-0000-0000-000000000000 | 008 | 1

Hope this makes more sense

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-14 : 11:24:49

select procductno, number, pcount
from table
where productno in (select max(productno) from table group by pcount)

pmw
Starting Member

6 Posts

Posted - 2004-05-14 : 11:36:18

I get the same error

Server: Msg 409, Level 16, State 2, Line 1
The maximum aggregate operation cannot take a uniqueidentifier data type as an argument.

It doesn't like the MAX(productno)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-14 : 11:50:22

[code]select productno, number, pcount
from table
where convert(binary,productno) in (select max(convert(binary,productno)) from table group by pcount)[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-14 : 12:00:09

Damn Jay...that's good....


USE Northwind
GO

CREATE TABLE myTable99(PRODUCTNO varchar(100), NUMBER varchar(10), PCOUNT int)
GO

INSERT INTO myTable99(PRODUCTNO, NUMBER, PCOUNT)
SELECT '00000264-0000-0000-0000-000000000000', '- E', 2 UNION ALL
SELECT '00000013-0000-0000-0000-000000000000', '- E', 1 UNION ALL
SELECT '00000265-0000-0000-0000-000000000000', '- R', NULL UNION ALL
SELECT '00000266-0000-0000-0000-000000000000', '- R', NULL UNION ALL
SELECT '000017D4-0000-0000-0000-000000000000', '008', 1 UNION ALL
SELECT '000017D5-0000-0000-0000-000000000000', '008', 1
GO

SELECT productno, number, pcount
  FROM myTable99
 WHERE CONVERT(binary,productno) 
    IN (SELECT MAX(CONVERT(binary,productno)) 
	  FROM myTable99 
      GROUP BY pcount)
GO

DROP TABLE myTable99
GO

EDIT: Preston's does the same thing though...why the conversion?

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-14 : 12:02:38

Thanks, I think I saw Mr Fribble use the convert(binary,NEWID()) to generate some randomizing functions before.

quote:
Preston's does the same thing though...why the conversion?

Yea, Preston and Amethystium have the concept, just have to convert the NEWID() to perform the aggregate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources