May 18, 2012 at 5:59 am
difference between last(max) and penultimate(max-1) for each group
I have:
col1 col2
a 1
a 2
a 3
b 5
b 6
b 7
c 3
c 4
c 5
max and max-1 refers to the data in column 2
I want:
col1 col2
a 3-2
b 7-6
c 5-4
Thankyou
May 18, 2012 at 6:05 am
add a rownumber() :
ie:
ROW_NUMBER() OVER (PARTITION BY [The text Column] ORDER BY [The text Column] ,[TheNumeric Column] ) As RW
with that, you could join the data against itself with an alias, comparing RW = RW -1, and get the difference.
in the future, if you were top provide actual CREATE TABLE and INSERT INTO statements, Any of the volunteers here could have given you an exact, working, tested solution.
Lowell
May 19, 2012 at 5:36 am
This would do it:
--Creating Table
Create Table Ex
(Col1 char(1),
Col2 int )
--Inserting Sample Data
Insert Into Ex
Select 'a', 1
Union ALL
Select 'a', 2
Union ALL
Select 'a', 3
Union ALL
Select 'b', 5
Union ALL
Select 'b', 6
Union ALL
Select 'b', 7
Union ALL
Select 'c', 3
Union ALL
Select 'c', 4
Union ALL
Select 'c', 5
--Query For Your Requirement
Select Col1, Cast(MAX(Col2) As Varchar)+'-'+Cast((MAX(Col2) - 1) As Varchar) As Col2 From Ex
Group By Col1
May 19, 2012 at 5:51 am
that would work or not work depending on the data, right vinu?
if the data elements are consecutive numbers, but if they are not, i don't think you get the right answer;
for example, if all the numbers incremented by ten instead of 1;
that's why you'd think row_number and joining the against itself is required.
Lowell
May 19, 2012 at 6:30 am
Lowell (5/19/2012)
that would work or not work depending on the data, right vinu?if the data elements are consecutive numbers, but if they are not, i don't think you get the right answer;
for example, if all the numbers incremented by ten instead of 1;
that's why you'd think row_number and joining the against itself is required.
Yes, you are right Lowell......posted it too soon....should have tested it more.
May 19, 2012 at 6:54 am
Lowell (5/19/2012)
that would work or not work depending on the data, right vinu?if the data elements are consecutive numbers, but if they are not, i don't think you get the right answer;
for example, if all the numbers incremented by ten instead of 1;
that's why you'd think row_number and joining the against itself is required.
yes, you are right, data are not consecutive numbers
I'll try Lowell's version with row numbers.
thanks
May 21, 2012 at 12:33 am
gelu01 (5/19/2012)
Lowell (5/19/2012)
that would work or not work depending on the data, right vinu?if the data elements are consecutive numbers, but if they are not, i don't think you get the right answer;
for example, if all the numbers incremented by ten instead of 1;
that's why you'd think row_number and joining the against itself is required.
yes, you are right, data are not consecutive numbers
I'll try Lowell's version with row numbers.
thanks
Sorry, I was a little busy the other day. I tried this the first thing I came to work today. Its a little complex, still didn't have much time. You might get a head start from this and, may be, come up with something simpler:
--Creating Table
Create Table Ex
(Col1 char(1),
Col2 int )
--Inserting Sample Data
Insert Into Ex
Select 'a', 1
Union ALL
Select 'a', 2
Union ALL
Select 'a', 3
Union ALL
Select 'b', 5
Union ALL
Select 'b', 6
Union ALL
Select 'b', 7
Union ALL
Select 'c', 3
Union ALL
Select 'c', 4
Union ALL
Select 'c', 5
--Query For Your Requirement
;With CTE
AS
(Select y.Col1, CAST(y.Col2 As Varchar(3))+'-'+CAST(x.Col2 As Varchar(3)) As Col2
From
(Select b.Col1, MAX(b.Col2) As Col2, ROW_NUMBER() Over (Order By (Select NULL) ) As rownum From
(Select *, ROW_NUMBER() Over (Partition By Col1 Order By Col2) As rownum From Ex) As b
Where b.rownum IN (Select (MAX(rownum) - 1) From (Select *, ROW_NUMBER() Over (Partition By Col1
Order By Col1) As rownum From Ex) As c Group By c.Col1) Group By b.Col1) As x
JOIN
(Select a.Col1, MAX(a.Col2) As Col2, ROW_NUMBER() Over (Order By (Select NULL) ) As rownum From
(Select *, ROW_NUMBER() Over (Partition By Col1 Order By Col1) As rownum From Ex) As a
Where a.rownum IN (Select (MAX(rownum)) From (Select *, ROW_NUMBER() Over (Partition By Col1
Order By Col1) As rownum From Ex) As d Group By d.Col1) Group By a.Col1) As y
ON x.rownum = y.rownum)
Select * From CTE
May 21, 2012 at 1:31 am
vinu512 (5/21/2012)
Sorry, I was a little busy the other day. I tried this the first thing I came to work today. Its a little complex, still didn't have much time. You might get a head start from this and, may be, come up with something simpler:
--Creating Table
Create Table Ex
(Col1 char(1),
Col2 int )
--Inserting Sample Data
Insert Into Ex
Select 'a', 1
Union ALL
Select 'a', 2
Union ALL
Select 'a', 3
Union ALL
Select 'b', 5
Union ALL
Select 'b', 6
Union ALL
Select 'b', 7
Union ALL
Select 'c', 3
Union ALL
Select 'c', 4
Union ALL
Select 'c', 5
--Query For Your Requirement
;With CTE
AS
(Select y.Col1, CAST(y.Col2 As Varchar(3))+'-'+CAST(x.Col2 As Varchar(3)) As Col2
From
(Select b.Col1, MAX(b.Col2) As Col2, ROW_NUMBER() Over (Order By (Select NULL) ) As rownum From
(Select *, ROW_NUMBER() Over (Partition By Col1 Order By Col2) As rownum From Ex) As b
Where b.rownum IN (Select (MAX(rownum) - 1) From (Select *, ROW_NUMBER() Over (Partition By Col1
Order By Col1) As rownum From Ex) As c Group By c.Col1) Group By b.Col1) As x
JOIN
(Select a.Col1, MAX(a.Col2) As Col2, ROW_NUMBER() Over (Order By (Select NULL) ) As rownum From
(Select *, ROW_NUMBER() Over (Partition By Col1 Order By Col1) As rownum From Ex) As a
Where a.rownum IN (Select (MAX(rownum)) From (Select *, ROW_NUMBER() Over (Partition By Col1
Order By Col1) As rownum From Ex) As d Group By d.Col1) Group By a.Col1) As y
ON x.rownum = y.rownum)
Select * From CTE
Whoa there, a bit overcomplicated don't you think?
How about this: -
WITH CTE(Col1,Col2,rn) AS (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2 DESC)
FROM Ex)
SELECT a.Col1,
CAST(a.Col2 AS VARCHAR(2))+'-'+ISNULL(CAST(b.Col2 AS VARCHAR(2)),'0') AS Col2
FROM (SELECT Col1, Col2, rn
FROM CTE
WHERE rn = 1) a
LEFT OUTER JOIN (SELECT Col1, Col2, rn
FROM CTE
WHERE rn = 2) b ON a.Col1 = b.Col1 AND a.rn=b.rn-1;
Returns: -
Col1 Col2
---- -----
a 3-2
b 7-6
c 5-4
The timings for both queries are pretty much irrelevant, as this is such a small amount of data they'd probably both manage 0ms, so let's take a look at the IO.
Your query: -
Table 'Worktable'. Scan count 2, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ex'. Scan count 4, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Mine: -
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ex'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
We could even make it simpler with an OUTER APPLY.
Here's one: -
SELECT a.Col1,
CAST(MAX(a.Col2) AS VARCHAR(2))+'-'+ISNULL(CAST(MAX(b.Col2) AS VARCHAR(2)),'0') AS Col2
FROM Ex a
OUTER APPLY (SELECT TOP 1 Col1, Col2
FROM Ex
WHERE a.Col1 = Col1 AND a.Col2 > Col2
ORDER BY Col2 DESC) b(Col1,Col2)
GROUP BY a.Col1;
And another: -
SELECT a.Col1,
CAST(MAX(a.Col2) AS VARCHAR(2))+'-'+ISNULL(CAST(MAX(b.Col2) AS VARCHAR(2)),'0') AS Col2
FROM Ex a
OUTER APPLY (SELECT Col1, MAX(Col2)
FROM Ex
WHERE a.Col1 = Col1 AND a.Col2 > Col2
GROUP BY Col1) b(Col1,Col2)
GROUP BY a.Col1;
Bother have identical IO
Table 'Ex'. Scan count 10, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
May 21, 2012 at 2:41 am
Lot's of JOINs and APPLYs in the house.
I'd do it with a subquery because it looks easier on the eyes.
DECLARE @t TABLE (Col1 char(1), Col2 int )
Insert Into @t
Select 'a', 1 Union ALL Select 'a', 2 Union ALL Select 'a', 3 Union ALL Select 'b', 5
Union ALL Select 'b', 6 Union ALL Select 'b', 7 Union ALL Select 'c', 3 Union ALL Select 'c', 4
Union ALL Select 'c', 5
SELECT Col1
,MAX(Col2)-ISNULL((
SELECT TOP 1 Col2
FROM @t t2
WHERE t1.Col1 = t2.Col1 AND MAX(t1.Col2) > t2.Col2
ORDER BY Col2 DESC),0) As Diff
FROM @t t1
GROUP BY Col1
IO stats:
Table '#15E88A7B'. Scan count 4, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 2:50 am
dwain.c (5/21/2012)
Lot's of JOINs and APPLYs in the house.I'd do it with a subquery because it looks easier on the eyes.
DECLARE @t TABLE (Col1 char(1), Col2 int )
Insert Into @t
Select 'a', 1 Union ALL Select 'a', 2 Union ALL Select 'a', 3 Union ALL Select 'b', 5
Union ALL Select 'b', 6 Union ALL Select 'b', 7 Union ALL Select 'c', 3 Union ALL Select 'c', 4
Union ALL Select 'c', 5
SELECT Col1
,MAX(Col2)-ISNULL((
SELECT TOP 1 Col2
FROM @t t2
WHERE t1.Col1 = t2.Col1 AND MAX(t1.Col2) > t2.Col2
ORDER BY Col2 DESC),0) As Diff
FROM @t t1
GROUP BY Col1
IO stats:
Table '#15E88A7B'. Scan count 4, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Think you forgot to do some casting there. . .
You produce: -
Col1 Diff
---- -----------
a 1
b 1
c 1
Expected result: -
Col1 Col2
---- -----
a 3-2
b 7-6
c 5-4
To achieve this, I guess you need: -
SELECT Col1,
CAST(MAX(Col2) AS VARCHAR(2)) + '-' +
ISNULL(CAST((SELECT TOP 1 Col2
FROM @t t2
WHERE t1.Col1 = t2.Col1 AND MAX(t1.Col2) > t2.Col2
ORDER BY Col2 DESC) AS VARCHAR(2)), '0') AS Diff
FROM @t t1
GROUP BY Col1;
May 21, 2012 at 3:05 am
Cadavre (5/21/2012)
Think you forgot to do some casting there. . .
Didn't forget, just didn't do.
It was unclear to me if the OP wanted to actually subtract the rows or display the character string result the rest of you guys were doing. As you demonstrated, the conversion was pretty straightforward.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 4:33 am
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply