June 18, 2010 at 9:48 am
Hi,
For example, I have a table like this:
IDvalue
15
25
37
47
57
67
79
89
95
105
117
......
How can I find out the IDs where the value changes? in this case, they would be 3, 7, 9 and 11.
Thanks
June 18, 2010 at 9:56 am
try:
select id
from mytable
group by id
having min(val) != max(val)
June 18, 2010 at 10:18 am
Thanks. It doesn't work though. The ID column is an identity column...
Eugene Elutin (6/18/2010)
try:
select id
from mytable
group by id
having min(val) != max(val)
June 18, 2010 at 10:26 am
declare @t_temp table (ID int, Value int)
insert into @t_temp
select 1, 5 union
select 2, 5 union
select 3, 7 union
select 4, 7 union
select 5, 7 union
select 6, 7 union
select 7, 9 union
select 8, 9 union
select 9, 5 union
select 10, 5 union
select 11, 7
select t1.ID, t1.Value
from @t_temp t1
join @t_temp t2
on t2.ID = t1.ID - 1
where t1.Value <> t2.Value
June 18, 2010 at 10:31 am
Thanks!!! This is it.
bteraberry (6/18/2010)
declare @t_temp table (ID int, Value int)insert into @t_temp
select 1, 5 union
select 2, 5 union
select 3, 7 union
select 4, 7 union
select 5, 7 union
select 6, 7 union
select 7, 9 union
select 8, 9 union
select 9, 5 union
select 10, 5 union
select 11, 7
select t1.ID, t1.Value
from @t_temp t1
join @t_temp t2
on t2.ID = t1.ID - 1
where t1.Value <> t2.Value
June 18, 2010 at 10:33 am
Be careful about gaps in the identity column,
June 18, 2010 at 10:49 am
Steve Jones - Editor (6/18/2010)
Be careful about gaps in the identity column,
Thanks Steve... This is exactly what I am thinking now. Let's say there's another column in the table
ID Value Model
1 5 win32
2 5 win32
3 7 win32
4 7 win32
5 7 win32
6 7 win32
7 9 win64
8 9 win64
9 5 win32
10 5 win32
11 7 win64
....
I only want to find out changes on model "win32", in this case, the IDs would be 3,9.... plus, the ID column might have gaps. What's the solution?
Thanks
June 18, 2010 at 11:20 am
Do you only care about changes in the platform? You need to use a subquery to find the previous value. Here's a basic query. Not sure it's what you want, but this is the idea.
declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))
insert into @t_temp
select 1, 5, 'Win32' union
select 2, 5, 'Win32' union
select 3, 7, 'Win32' union
select 4, 7, 'Win32' union
select 5, 7, 'Win32' union
select 6, 7, 'Win32' union
select 7, 9, 'Win64' union
select 8, 9, 'Win64' union
select 9, 5, 'Win32' union
select 10,5, 'Win32' union
select 12,7, 'Win64' UNION
select 13,7, 'Win64'
select t1.ID, t1.VALUE, t1.Value2
from @t_temp t1, @t_temp t2
where t1.id > t2.id
and t1.Value2 <> t2.VALUE2
AND t2.id = (SELECT MAX( id)
FROM @t_temp t3
WHERE t3.id < t1.ID
)
June 18, 2010 at 12:14 pm
Thanks Steve.
This is what I got from abdshall@Technet, and it works:
DECLARE @t TABLE(ID INT,Value INT,Model VARCHAR(50))
INSERT INTO @t
SELECT 1, 5, 'win32' UNION ALL
SELECT 2, 5, 'win32' UNION ALL
SELECT 3, 7, 'win32' UNION ALL
SELECT 4, 7, 'win32' UNION ALL
SELECT 5, 7, 'win32' UNION ALL
SELECT 6, 7, 'win32' UNION ALL
SELECT 7, 9, 'win64' UNION ALL
SELECT 8, 9, 'win64' UNION ALL
SELECT 9, 5, 'win32' UNION ALL
SELECT 10, 5, 'win32' UNION ALL
SELECT 11, 7, 'win64'
;WITH CTE AS
(
SELECT ID,Value,Model,ROW_NUMBER() OVER(ORDER BY ID) -
ROW_NUMBER() OVER(ORDER BY Value,id) AS Row2
FROM @t
WHERE Model = 'Win32'
)
SELECT MIN(ID) AS ID,Value,Model
FROM CTE
WHERE Row2 <> 0
GROUP BY value,Row2,Model
ORDER BY ID
Steve Jones - Editor (6/18/2010)
Do you only care about changes in the platform? You need to use a subquery to find the previous value. Here's a basic query. Not sure it's what you want, but this is the idea.
declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))insert into @t_temp
select 1, 5, 'Win32' union
select 2, 5, 'Win32' union
select 3, 7, 'Win32' union
select 4, 7, 'Win32' union
select 5, 7, 'Win32' union
select 6, 7, 'Win32' union
select 7, 9, 'Win64' union
select 8, 9, 'Win64' union
select 9, 5, 'Win32' union
select 10,5, 'Win32' union
select 12,7, 'Win64' UNION
select 13,7, 'Win64'
select t1.ID, t1.VALUE, t1.Value2
from @t_temp t1, @t_temp t2
where t1.id > t2.id
and t1.Value2 <> t2.VALUE2
AND t2.id = (SELECT MAX( id)
FROM @t_temp t3
WHERE t3.id < t1.ID
)
June 21, 2010 at 7:27 am
Hi all,
Are you sure that the previous solution works successfully?
I could not understand why we need to set the win32 in the WHERE clause of the CTE
I think Steve has a better solution suggesstion.
But I would merge the CTE part of the previous solution to prevent gap problems with the Steve's solution.
June 21, 2010 at 7:55 am
Here is my code suggestion :
with cte as (
select *, rn = ROW_NUMBER() OVER (Order By Id) from FindChanges
)
select t1.*, t2.*
from cte t1
full join cte t2 on t1.rn = t2.rn + 1
where t1.model <> t2.model
June 21, 2010 at 10:40 am
Hi, Erapler
I used your solution, but did not get what I really want, did I miss anything? Thanks
DECLARE @t TABLE(ID INT,Value INT,Model VARCHAR(50))
INSERT INTO @t
SELECT 1, 5, 'win32' UNION ALL
SELECT 2, 5, 'win32' UNION ALL
SELECT 3, 7, 'win32' UNION ALL
SELECT 4, 7, 'win32' UNION ALL
SELECT 5, 7, 'win32' UNION ALL
SELECT 6, 7, 'win32' UNION ALL
SELECT 7, 9, 'win64' UNION ALL
SELECT 8, 9, 'win64' UNION ALL
SELECT 9, 5, 'win32' UNION ALL
SELECT 10, 5, 'win32' UNION ALL
SELECT 11, 7, 'win64'
;with cte as (
select *, rn = ROW_NUMBER() OVER (Order By Id) from @t
)
select t1.*, t2.*
from cte t1
full join cte t2 on t1.rn = t2.rn + 1
where t1.model <> t2.model
The result is:
IDValueModelrnIDValueModelrn
79win64767win326
95win32989win648
117win6411105win3210
Eralper (6/21/2010)
Here is my code suggestion :
with cte as (select *, rn = ROW_NUMBER() OVER (Order By Id) from FindChanges
)
select t1.*, t2.*
from cte t1
full join cte t2 on t1.rn = t2.rn + 1
where t1.model <> t2.model
June 21, 2010 at 4:12 pm
declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))
insert into @t_temp
select 1, 5, 'Win32' union
select 2, 5, 'Win32' union
select 3, 7, 'Win32' union
select 4, 7, 'Win32' union
select 5, 7, 'Win32' union
select 6, 7, 'Win32' union
select 7, 9, 'Win64' union
select 8, 9, 'Win64' union
select 9, 5, 'Win32' union
select 10,5, 'Win32' union
select 12,7, 'Win64' union
select 13,7, 'Win64';
with cteTemp(ID, Value, RowNum)
as
(
select ID,
Value,
ROW_NUMBER() OVER(ORDER BY ID)
from @t_temp
where Value2 = 'Win32'
)
select cc.ID
from cteTemp cc
join cteTemp cp
on cp.RowNum = cc.RowNum - 1
where cc.Value <> cp.Value
June 21, 2010 at 5:07 pm
Thanks. Got it. Basically add a column rownumber as identity.
bteraberry (6/21/2010)
declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))insert into @t_temp
select 1, 5, 'Win32' union
select 2, 5, 'Win32' union
select 3, 7, 'Win32' union
select 4, 7, 'Win32' union
select 5, 7, 'Win32' union
select 6, 7, 'Win32' union
select 7, 9, 'Win64' union
select 8, 9, 'Win64' union
select 9, 5, 'Win32' union
select 10,5, 'Win32' union
select 12,7, 'Win64' union
select 13,7, 'Win64';
with cteTemp(ID, Value, RowNum)as
(
select ID,
Value,
ROW_NUMBER() OVER(ORDER BY ID)
from @t_temp
where Value2 = 'Win32'
)
select cc.ID
from cteTemp cc
join cteTemp cp
on cp.RowNum = cc.RowNum - 1
where cc.Value <> cp.Value
June 21, 2010 at 11:46 pm
Hi pcgm,
My code responds to changes in model
If you want to respond to changes in value column please change the WHERE clause as follows
t1.value <> t2.value --t1.model <> t2.model
I mean
with cte as (
select *, rn = ROW_NUMBER() OVER (Order By Id) from FindChanges
)
select t1.*, t2.*
from cte t1
full join cte t2 on t1.rn = t2.rn + 1
where t1.value <> t2.value
I hope I'm not missing something too.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply