Complicated grouping data
-
I have a view as description below, let call it ViewA:
A B C V
a1 0 11 v1
a1 1 12 v2
a1 0 21 v3
a1 1 22 v4
a2 1 21 v5
a2 1 22 v6
Now i want to select from ViewA so that the result is like this:
A C V
a1 21 v3
a2 22 v6
The process of selecting can described as this (sorry for my bad English :-P) :
For each distinct A value
If exists B = 0 then select max(C), V which C = max(C) where B = 0 group by A
else select max(C), V which C = max(C) group by A
Hope that you can make sense what i describe.
The view have about 100 000 records.
I need to write the query in T-SQL (2008) (not using linq). It is acceptable if the query take minutes to complete.
I try this sollution but it seem to be complicated:
SELECTViewA.A
, T4.C
, ViewA.V
FROM
ViewA
INNER JOIN
(
SELECT T3.A, MAX(T3.C) AS C
FROM
(SELECT
T1.A, T1.C, T2.A AS AA
FROM
ViewA AS T1
LEFT JOIN ViewA AS T2 ON T1.A = T2.A AND T2.B = 0
WHERE
T1.B = 0 OR T2.AA IS NULL
) AS T3
GROUP BY T3.A
) AS T4
ON
ViewA.A = T4.A AND
ViewA.C = T4.C
Could someone suggest any other solution?
-
It would help if you provided the table defination(s) as CREATE TABLE statement(s) and the sample data as a series of INSERT INTO statements.
-
Untested...
WITH CTE AS (SELECT A,B,C,V,
ROW_NUMBER() OVER(PARTITION BY A ORDER BY B,C DESC) AS rn
FROM ViewA)
SELECT A,C,V
FROM CTE
WHERE rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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
-
CREATE TABLE ViewA(
A CHAR(2)
, B INT
, C INT
, V CHAR(2)
);
INSERT INTO ViewA
SELECT
'a1', 0, 11, 'v1';
INSERT INTO ViewA
SELECT
'a1', 1, 12, 'v2';
INSERT INTO ViewA
SELECT
'a1', 0, 21, 'v3';
INSERT INTO ViewA
SELECT
'a1', 1, 22, 'v4';
INSERT INTO ViewA
SELECT
'a2', 1, 21, 'v5';
INSERT INTO ViewA
SELECT
'a2', 1, 22, 'v6';
Please help, thanks!
-
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.TestTableselect '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.
-
Any other solution, please?:-)
-
Just a slight modification to Lynn's solution : replace
order by Bwithorder by abs(sign(B))Here's the full script:
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',-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, 'v4';
with OrderVersion as (
select
row_number() over (partition by A order by abs(sign(B)) asc, C desc) as RowNum,
A,
B,
C,
V
from
dbo.TestTable
)
select
A,
C,
V
from
OrderVersion
where
RowNum = 1
Peter
-
If I understand the original English description of the requirement correctly, this appears correct:
DECLARE @data TABLE
(
ACHAR(2) NOT NULL,
BINT NOT NULL,
CINT NOT NULL,
VCHAR(2) NOT NULL
);
INSERT@data
VALUES('a1', 0, 11, 'v1'),
('a1', 1, 12, 'v2'),
('a1',-1, 12, 'v2'),
('a1', 0, 21, 'v3'),
('a1', 1, 22, 'v4'),
('a2', 1, 21, 'v5'),
('a2',-1, 12, 'v6'),
('a2', 1, 22, 'v6');
SELECTA.A,
CA.C,
CA.V
FROM(
SELECTDISTINCT D.A
FROM@data D
) A
CROSS
APPLY(
SELECTTOP (1)
D2.C,
D2.V
FROM@data D2
WHERED2.A = A.A
ORDERBY
CASE WHEN B <> 0 THEN 0 ELSE -1 END,
D2.C DESC
) CA;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White (1/9/2010)
If I understand the original English description of the requirement correctly, this appears correct:DECLARE @data TABLE
(
ACHAR(2) NOT NULL,
BINT NOT NULL,
CINT NOT NULL,
VCHAR(2) NOT NULL
);
INSERT@data
VALUES('a1', 0, 11, 'v1'),
('a1', 1, 12, 'v2'),
('a1',-1, 12, 'v2'),
('a1', 0, 21, 'v3'),
('a1', 1, 22, 'v4'),
('a2', 1, 21, 'v5'),
('a2',-1, 12, 'v6'),
('a2', 1, 22, 'v6');
SELECTA.A,
CA.C,
CA.V
FROM(
SELECTDISTINCT D.A
FROM@data D
) A
CROSS
APPLY(
SELECTTOP (1)
D2.C,
D2.V
FROM@data D2
WHERED2.A = A.A
ORDERBY
CASE WHEN B <> 0 THEN 0 ELSE -1 END,
D2.C DESC
) CA;
This is what I'm looking for.
Thanks a lot, White. You've saved me from working on Sunday 🙂
-
Nothing worse than working on a Sunday 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I am using Execution Plan and I've got a result:
With 1000000 record and 100000 A distinct value:
- SQL using over (partition): 5%
- SQL using cross apply: 95%
With 1000000 record and 10 A distinct value:
- SQL using over (partition): 43%
- SQL using cross apply: 57%
Why?
-
I assume the percentage figures you quote are the cost % of the batch? I don't generally attach much value to those percentages, but it'd be great to see the plans if you wouldn't mind uploading them. Run the two statements with 'Include Actual Execution Plan' on, right click the graphical plans and save them as .sqlplan files. Thanks.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
So sorry, my company don't allow upload file.
<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.1600.22" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="103354" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1362.28" StatementText=" SELECT A.A, CA.C, CA.V FROM ( SELECT DISTINCT D.A FROM TestTable D ) A CROSS APPLY ( SELECT TOP (1) D2.C, D2.V FROM TestTable D2 WHERE D2.A = A.A ORDER BY CASE WHEN B <> 0 THEN 0 ELSE -1 END, D2.C DESC ) CA; " StatementType="SELECT" QueryHash="0x58E24FB05BE87DCA" QueryPlanHash="0xE7F0C5AEB456DB56">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="24" CompileTime="5" CompileCPU="5" CompileMemory="224">
<RelOp AvgRowSize="63" EstimateCPU="0.360655" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="103354" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1362.28">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
</OutputList>
<Parallelism>
<RelOp AvgRowSize="63" EstimateCPU="0.21601" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="103354" LogicalOp="Inner Join" NodeId="1" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1361.92">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</OuterReferences>
<RelOp AvgRowSize="57" EstimateCPU="5.64518" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="103354" LogicalOp="Aggregate" NodeId="2" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="18.115">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</OutputList>
<MemoryFractions Input="1" Output="0.999446" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[master].[dbo].[TestTable].[A] as [D].[A] = [master].[dbo].[TestTable].[A] as [D].[A]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="57" EstimateCPU="3.95213" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Repartition Streams" NodeId="3" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="12.4698">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</PartitionColumns>
<RelOp AvgRowSize="57" EstimateCPU="0.550039" EstimateIO="7.96765" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Table Scan" NodeId="4" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="8.51769" TableCardinality="1000000">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="17" EstimateCPU="0.000149434" EstimateIO="0.0112613" EstimateRebinds="103353" EstimateRewinds="0" EstimateRows="1" LogicalOp="TopN Sort" NodeId="6" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1343.58">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
<ColumnReference Column="Expr1006" />
</OutputList>
<MemoryFractions Input="0.000554017" Output="0.000554017" />
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1006" />
</OrderByColumn>
<OrderByColumn Ascending="false">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="17" EstimateCPU="1.00027" EstimateIO="63.155" EstimateRebinds="103353" EstimateRewinds="0" EstimateRows="9.67485" LogicalOp="Eager Spool" NodeId="7" Parallel="true" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="164.238">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
<ColumnReference Column="Expr1006" />
</OutputList>
<Spool>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="A" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[master].[dbo].[TestTable].[A] as [D].[A]">
<Identifier>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D]" Column="A" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<RelOp AvgRowSize="67" EstimateCPU="0.1" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Compute Scalar" NodeId="8" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.16773">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CASE WHEN [master].[dbo].[TestTable]. as [D2].<>(0) THEN (0) ELSE (-1) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="B" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(-1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="67" EstimateCPU="1.10016" EstimateIO="7.96757" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Table Scan" NodeId="9" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="9.06773" TableCardinality="1000000">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="B" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="A" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="B" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="C" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" Column="V" />
</DefinedValue>
</DefinedValues>
<Object Database="[master]" Schema="[dbo]" Table="[TestTable]" Alias="[D2]" IndexKind="Heap" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Spool>
</RelOp>
</TopSort>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="31622.8" StatementId="2" StatementOptmLevel="FULL" StatementSubTreeCost="70.8492" StatementText=" 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 " StatementType="SELECT" QueryHash="0x34DE5CA7CA1D7AF5" QueryPlanHash="0xCDB89680D8334FED">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="24" CompileTime="3" CompileCPU="3" CompileMemory="160">
<RelOp AvgRowSize="63" EstimateCPU="0.130128" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="31622.8" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="70.8492">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
</OutputList>
<Parallelism>
<RelOp AvgRowSize="63" EstimateCPU="0.24" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="31622.8" LogicalOp="Filter" NodeId="1" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="70.7191">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
</OutputList>
<Filter StartupExpression="false">
<RelOp AvgRowSize="71" EstimateCPU="7.491" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Distribute Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="70.4791">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
<ColumnReference Column="Expr1004" />
</OutputList>
<Parallelism PartitioningType="RoundRobin">
<RelOp AvgRowSize="71" EstimateCPU="0.08" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="62.9881">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
<ColumnReference Column="Expr1004" />
</OutputList>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="71" EstimateCPU="0.02" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Segment" NodeId="4" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="62.9081">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
<ColumnReference Column="Segment1005" />
</OutputList>
<Segment>
<GroupBy>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1005" />
</SegmentColumn>
<RelOp AvgRowSize="67" EstimateCPU="8.62175" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Gather Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="62.8881">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
</OutputList>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
</OrderByColumn>
<OrderByColumn Ascending="false">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="67" EstimateCPU="45.743" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Sort" NodeId="6" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="54.2663">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
</OrderByColumn>
<OrderByColumn Ascending="false">
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="67" EstimateCPU="0.550039" EstimateIO="7.96765" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Table Scan" NodeId="7" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="8.51769" TableCardinality="1000000">
<OutputList>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="A" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="B" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="C" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[master]" Schema="[dbo]" Table="[TestTable]" Column="V" />
</DefinedValue>
</DefinedValues>
<Object Database="[master]" Schema="[dbo]" Table="[TestTable]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Parallelism>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
</Parallelism>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[Expr1004]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1004" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Parallelism>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
-
Right. It took a fair amount of work to get to see the plans there, but I now see what you mean.
The APPLY version is much less efficient than the row_number() version for large numbers of rows because it needs to do a join, amongst other things.
If you incorporate the tweak suggested by Peter Brinkhaus a while back, you should find that produces the results you want:
;with OrderVersion as (
select
row_number() over (partition by A order by abs(sign(B)) asc, C desc) as RowNum,
A,
B,
C,
V
from
dbo.TestTable
)
select
A,
C,
V
from
OrderVersion
where
RowNum = 1
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply