April 25, 2010 at 7:26 pm
This is my solution
declare @TableVar table (
Key1 int NOT NULL
, VAL1 int NOT NULL
, VAL2 int NOT NULL
, SOMEVALUE int NOT NULL
)
INSERT INTO @TableVar
SELECT 1 as KEY1, 10 as VAL1, 151 as VAL2, 1 as SOMEVALUE
UNION ALL SELECT 1 as KEY1, 11 as VAL1, 161 as VAL2, 2 as SOMEVALUE
UNION ALL SELECT 1 as KEY1, 12 as VAL1, 141 as VAL2, 1 as SOMEVALUE
UNION ALL SELECT 2 as KEY1, 13 as VAL1, 121 as VAL2, 3 as SOMEVALUE
UNION ALL SELECT 2 as KEY1, 14 as VAL1, 131 as VAL2, 4 as SOMEVALUE
UNION ALL SELECT 2 as KEY1, 15 as VAL1, 111 as VAL2, 2 as SOMEVALUE
SELECT Key1, MAX(VAL1) as MAX1, SUM(VAL2) as SUM2 FROM @TableVar
GROUP BY KEY1
I want to get result is:
Key1 MAX1 SUM2 SOMEVALUE
1 12 453 1 (with max(val1)=12 => SOMEVALUE=1)
2 15 363 2 (with max(val1)=15 => SOMEVALUE=2)
please help me 😀
April 25, 2010 at 10:16 pm
This would work for u!
;WITH CTE (Key1,MAX1,SUM2)
AS
( SELECT Key1, MAX(VAL1) as MAX1, SUM(VAL2) as SUM2 FROM @TableVar
GROUP BY KEY1
)
SELECT TAB.Key1 , TAB.VAL1 MAX1 , CTE.SUM2, TAB.SOMEVALUE
FROM CTE CTE
JOIN @TableVar TAB
ON CTE.Key1 = TAB.Key1 AND CTE.MAX1 = TAB.VAL1
Tell us if that worked..
Cheers!!
April 26, 2010 at 12:27 am
Thanks coffee:-D. But can you rewrite with out subquery or CTE?
April 26, 2010 at 1:05 am
SELECT Key1, MAX(VAL1) as MAX1, SUM(VAL2) as SUM2 ,
(case when MAX(VAL1) = 12 then 1
when MAX(VAL1) = 15 then 2
else 0 end) SOMEVALUE FROM @TableVar
GROUP BY KEY1
April 26, 2010 at 1:47 am
nguyennd (4/26/2010)
Thanks coffee:-D. But can you rewrite with out subquery or CTE?
Ummmm.. I dont think we can acheive this without CTE or Subquery...
April 26, 2010 at 11:26 am
April 26, 2010 at 12:26 pm
Looks just as easy with a CTE:
with GroupData as (
SELECT
Key1,
MAX(VAL1) as MAX1,
SUM(VAL2) as SUM2
FROM
@TableVar
GROUP BY
Key1
)
select
gd.Key1,
gd.MAX1,
gd.SUM2,
tv.SOMEVALUE
from
GroupData gd
inner join @TableVar tv
on (gd.Key1 = tv.Key1
and gd.MAX1 = tv.VAL1);
April 26, 2010 at 1:56 pm
April 26, 2010 at 2:01 pm
bteraberry (4/26/2010)
He said he wanted arewrite with out subquery or CTE
but yes, without that stipulation the CTE would be plenty easy.
Why would we want to do that if we are supposedly using SQL Server 2008? CTE's make things easier to read instead of using derived tables in the FROM clause.
April 26, 2010 at 2:06 pm
Lynn Pettis (4/26/2010)
bteraberry (4/26/2010)
He said he wanted arewrite with out subquery or CTE
but yes, without that stipulation the CTE would be plenty easy.
Why would we want to do that if we are supposedly using SQL Server 2008? CTE's make things easier to read instead of using derived tables in the FROM clause.
Maybe he got a homework assignment to figure out a way to do it without a CTE or subquery?
Who knows, I was just curious from a standpoint of figuring out what possibilities there are with artificial limitations.
And happy Monday to you Lynn!
April 26, 2010 at 8:07 pm
Lynn Pettis (4/26/2010)
Looks just as easy with a CTE:
with GroupData as (
SELECT
Key1,
MAX(VAL1) as MAX1,
SUM(VAL2) as SUM2
FROM
@TableVar
GROUP BY
Key1
)
select
gd.Key1,
gd.MAX1,
gd.SUM2,
tv.SOMEVALUE
from
GroupData gd
inner join @TableVar tv
on (gd.Key1 = tv.Key1
and gd.MAX1 = tv.VAL1);
Lynn, i gave him the same code, but the OP wants it without CTEs/Subqueries... as Bteraberry said, it might just be his/her homework or rahter out of curosity..:-P
April 26, 2010 at 10:44 pm
Another way of doing it:
SELECT DV.Key1,
DV.MAX1,
DV.SUM2,
DV.SOMEVALUE
FROM (
SELECT Key1,
SOMEVALUE,
VAL1,
MAX1 = MAX(VAL1) OVER (PARTITION BY KEY1),
SUM2 = SUM(VAL2) OVER (PARTITION BY KEY1)
FROM @TableVar
) DV
WHERE DV.VAL1 = DV.MAX1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 11:09 pm
Thanks all
But my boss were change request:
My solution is very complex huhu
declare @Header table (
HeaderKey int NOT NULL
, CreateDate int NOT NULL
)
INSERT INTO @Header
SELECT 1 as HeaderKey, 2 as CreateDate
UNION ALL SELECT 2 as HeaderKey, 4 as CreateDate
UNION ALL SELECT 3 as HeaderKey, 5 as CreateDate
declare @Detail table (
HeaderKey int NOT NULL
, DetailKey int NOT NULL
, Price int NOT NULL
)
INSERT INTO @Detail
SELECT 1 as HeaderKey, 1 as DetailKey, 151 as Price
UNION ALL SELECT 1 as HeaderKey, 1 as DetailKey, 152 as Price
UNION ALL SELECT 2 as HeaderKey, 1 as DetailKey, 150 as Price
UNION ALL SELECT 2 as HeaderKey, 2 as DetailKey, 154 as Price
UNION ALL SELECT 3 as HeaderKey, 2 as DetailKey, 153 as Price
With my SQL
select
DetailKey,MAX(H.CreateDate) MAXDATE
from
@Header H
INNER JOIN @Detail D on H.HeaderKey = D.HeaderKey
GROUP BY
D.DetailKey
And I want to get data same above
DetailKey MAXDATE MAXPRICE
1 4 150 (with max CreateDate= 4 => HeaderKey=2 => Price = 150)
2 5 153
Please help me
April 26, 2010 at 11:38 pm
That's not very different - I'm surprised you couldn't work it out for yourself :Whistling:
SELECT DV.DetailKey,
DV.MAXDATE,
DV.Price
FROM (
SELECT D.DetailKey,
H.CreateDate,
D.Price,
MAXDATE = MAX(H.CreateDate) OVER (PARTITION BY D.DetailKey),
MAXPRICE = MAX(D.Price) OVER (PARTITION BY D.DetailKey)
FROM @Header H
JOIN @Detail D
ON D.HeaderKey = H.HeaderKey
) DV
WHERE DV.CreateDate = DV.MAXDATE;
By the way, what does 'huhu' mean? :unsure:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 27, 2010 at 12:27 am
Huhu = hic hic 😀
Thank Paul, very very ...
But why do you use
MAXPRICE = MAX(D.Price) OVER (PARTITION BY D.DetailKey)
, i don't understand 😀
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply