Complicated grouping data

Question

Complicated grouping data

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

khanhdp (1/8/2010)
Lynn Pettis (1/8/2010)
Tested, and looks like the same as above.
create table dbo.TestTable (
A char(2),
B int,
C int,
V char(2)
);
insert into dbo.TestTable
select 'a1',0, 11, 'v1' union all
select 'a1',1, 12, 'v2' union all
select 'a1',0, 21, 'v3' union all
select 'a1',1, 22, 'v4' union all
select 'a2',1, 21, 'v5' union all
select 'a2',1, 22, 'v6';
with OrderVersion as (
select
row_number() over (partition by A order by B asc, C desc) as RowNum,
A,
B,
C,
V
from
dbo.TestTable
)
select
A,
C,
V
from
OrderVersion
where
RowNum = 1
Thanks Pettis.
Your solution is good.
However, in this case of the TestTable:
insert into dbo.TestTable
select 'a1',0, 11, 'v1' union all
select 'a1',1, 12, 'v2' union all
select 'a1',-1, 12, 'v2' union all
select 'a1',0, 21, 'v3' union all
select 'a1',1, 22, 'v4' union all
select 'a2',1, 21, 'v5' union all
select 'a2',-1, 12, 'v6' union all
select 'a2',1, 22, 'v6';
the result is not what i expected.
The problem is the number of difference values of column B is unknown in my case and 0 is not the min value.

First chance I had to really get back and look at this one. I have to agree with Peter and Paul regarding the tweak to my code.

May suggest in the future you provide a more complete test suite of data? The code initially provided did meet the original requirements based on the sample data provided.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Lynn Pettis (1/11/2010)
May suggest in the future you provide a more complete test suite of data? The code initially provided did meet the original requirements based on the sample data provided.

Indeed. It is regrattable that nguyennd missed Peter's post first time around - it could have saved a bit of bother! I congratulate Peter on a fine solution by the way (though I think it's safe to lose the SIGN part, to just leave the ABS). The slightly scary thing was the reference to not being allowed to upload files from work...I thought this was a hobby question :blink:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

matzer SSCrazy Eights Points: 9727 More actions · Answer 3

Paul White (1/11/2010)
... I congratulate Peter on a fine solution by the way ...

Thanks, Paul.

...(though I think it's safe to lose the SIGN part, to just leave the ABS)...

The SIGN part isn't required if the only values of B are -1, 0 or 1 as in the new testset the OP posted. However, the OP also stated: The problem is the number of difference values of column B is unknown in my case and 0 is not the min value. . If B can be any integer SIGN is required to turn B into -1, 0 or 1. Then ABS is used to turn it into 0 or 1.

Peter

Paul White SSC Guru Points: 150467 More actions · Answer 4

Peter Brinkhaus (1/11/2010)
The SIGN part isn't required if the only values of B are -1, 0 or 1 as in the new testset the OP posted. However, the OP also stated: The problem is the number of difference values of column B is unknown in my case and 0 is not the min value. . If B can be any integer SIGN is required to turn B into -1, 0 or 1. Then ABS is used to turn it into 0 or 1.Peter

Quite so - it's just that the result of the ABS(SIGN(B)) is only used in the ORDER BY part of the ROW_NUMBER ranking function, so I would have thought that ABS(B) would always sort correctly (with zero first followed by anything else) so the SIGN seems a bit redundant?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

Paul White (1/11/2010)
Quite so - it's just that the result of the ABS(SIGN(B)) is only used in the ORDER BY part of the ROW_NUMBER ranking function, so I would have thought that ABS(B) would always sort correctly (with zero first followed by anything else) so the SIGN seems a bit redundant?

If I understand the requirements (and Lynn's solution) correctly, the order of B should be ignored when there is no record with B=0 for a particular value of A. For instance, consider the following set of values of (B, C): (-2, 10), (1, 5), (3, 1). Ordering by ABS(B) asc, C desc gives: (1, 5), (2, 10), (3, 1). But 5 is not the max in this case. Ordering by ABS(SIGN(B)) asc, C desc gives: (1, 10), (1, 5), (1, 1). So 10 is the max.

Or did I had too much coffee?

Peter

Paul White SSC Guru Points: 150467 More actions · Answer 6

Peter Brinkhaus (1/11/2010)
Paul White (1/11/2010)
Quite so - it's just that the result of the ABS(SIGN(B)) is only used in the ORDER BY part of the ROW_NUMBER ranking function, so I would have thought that ABS(B) would always sort correctly (with zero first followed by anything else) so the SIGN seems a bit redundant?
If I understand the requirements (and Lynn's solution) correctly, the order of B should be ignored when there is no record with B=0 for a particular value of A. For instance, consider the following set of values of (B, C): (-2, 10), (1, 5), (3, 1). Ordering by ABS(B) asc, C desc gives: (1, 5), (2, 10), (3, 1). But 5 is not the max in this case. Ordering by ABS(SIGN(B)) asc, C desc gives: (1, 10), (1, 5), (1, 1). So 10 is the max.

Ah right I see now - thanks for taking the time to explain that so well. CAST(B AS BIT) would work too I think.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

matzer SSCrazy Eights Points: 9727 More actions · Answer 7

Paul White (1/11/2010)
CAST(B AS BIT) would work too I think.

Nice tweak, which probably saves you a couple of clock cylces.

Peter

khanhdp SSC Enthusiast Points: 118 More actions · Answer 8

Hi all,

First i'm sorry that i didn't provide a clear requirement.

I provided the specific value of B, 0, in order to keep the problem simple. However, in fact it must be a general number, which is passed as a parameter.(The value of the parameter is current 0, but requirement always changes)

That why i said that the sollution of Mr White was exact what i need.

Anyway, i have learned many trick from you.

Thanks a lot for help 🙂