Mixing
Selecting rows where a value has changed
1
I have a table that shows a products price on a daily basis. A product has 3 prices: wholesale, volume and clearance.
The table is updated everyday with the current prices, most days no changes occur. However, I want to select the rows where one of the values has changed and I'm struggling for ideas. I've tried a group by but because prices go up and down this won't work.
select date_entered,
WholeSale,
Volume,
Clearance
FROM pricetable
where product = 'TANGO'
order by date_entered desc
The above selects all the rows from the table for a set product.
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred.
Any help appreciated.
sql
sql-server tsql sql-server-2008-r2 distinct edited Nov 20 '18 at 10:11
Salman A
176k66336424
asked Nov 19 '18 at 16:34
benjiiiii
168219
|
show 7 more comments
1
I have a table that shows a products price on a daily basis. A product has 3 prices: wholesale, volume and clearance.
The table is updated everyday with the current prices, most days no changes occur. However, I want to select the rows where one of the values has changed and I'm struggling for ideas. I've tried a group by but because prices go up and down this won't work.
select date_entered,
WholeSale,
Volume,
Clearance
FROM pricetable
where product = 'TANGO'
order by date_entered desc
The above selects all the rows from the table for a set product.
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred.
Any help appreciated.
sql
sql-server tsql sql-server-2008-r2 distinct edited Nov 20 '18 at 10:11
Salman A
176k66336424
asked Nov 19 '18 at 16:34
benjiiiii
168219
Tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 16:35
SQLis a language, you need to tag your question with DBMS your using.eg: SQL Server, MySQL, SQLite ...
– Sami
Nov 19 '18 at 16:38
Apologies, I've added the tag now.
– benjiiiii
Nov 19 '18 at 16:40
You can useLAG()to get the prev data and then compare it with the current data. PrevData <> CurrentData
– Sami
Nov 19 '18 at 16:42
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred a change since when? The previous day?
– forpas
Nov 19 '18 at 16:43
|
show 7 more comments
1
1
1
I have a table that shows a products price on a daily basis. A product has 3 prices: wholesale, volume and clearance.
The table is updated everyday with the current prices, most days no changes occur. However, I want to select the rows where one of the values has changed and I'm struggling for ideas. I've tried a group by but because prices go up and down this won't work.
select date_entered,
WholeSale,
Volume,
Clearance
FROM pricetable
where product = 'TANGO'
order by date_entered desc
The above selects all the rows from the table for a set product.
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred.
Any help appreciated.
sql
sql-server tsql sql-server-2008-r2 distinct edited Nov 20 '18 at 10:11
Salman A
176k66336424
asked Nov 19 '18 at 16:34
benjiiiii
168219
I have a table that shows a products price on a daily basis. A product has 3 prices: wholesale, volume and clearance.
The table is updated everyday with the current prices, most days no changes occur. However, I want to select the rows where one of the values has changed and I'm struggling for ideas. I've tried a group by but because prices go up and down this won't work.
select date_entered,
WholeSale,
Volume,
Clearance
FROM pricetable
where product = 'TANGO'
order by date_entered desc
The above selects all the rows from the table for a set product.
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred.
Any help appreciated.
sql
sql-server tsql sql-server-2008-r2 distinct sql
sql-server tsql sql-server-2008-r2 distinct edited Nov 20 '18 at 10:11
Salman A
176k66336424
asked Nov 19 '18 at 16:34
benjiiiii
168219
edited Nov 20 '18 at 10:11
Salman A
176k66336424
asked Nov 19 '18 at 16:34
benjiiiii
168219
edited Nov 20 '18 at 10:11
Salman A
176k66336424
edited Nov 20 '18 at 10:11
Salman A
176k66336424
edited Nov 20 '18 at 10:11
Salman A
176k66336424
176k66336424
asked Nov 19 '18 at 16:34
benjiiiii
168219
asked Nov 19 '18 at 16:34
benjiiiii
168219
asked Nov 19 '18 at 16:34
benjiiiii
168219
168219
Tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 16:35
SQLis a language, you need to tag your question with DBMS your using.eg: SQL Server, MySQL, SQLite ...
– Sami
Nov 19 '18 at 16:38
Apologies, I've added the tag now.
– benjiiiii
Nov 19 '18 at 16:40
You can useLAG()to get the prev data and then compare it with the current data. PrevData <> CurrentData
– Sami
Nov 19 '18 at 16:42
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred a change since when? The previous day?
– forpas
Nov 19 '18 at 16:43
|
show 7 more comments
Tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 16:35
SQLis a language, you need to tag your question with DBMS your using.eg: SQL Server, MySQL, SQLite ...
– Sami
Nov 19 '18 at 16:38
Apologies, I've added the tag now.
– benjiiiii
Nov 19 '18 at 16:40
You can useLAG()to get the prev data and then compare it with the current data. PrevData <> CurrentData
– Sami
Nov 19 '18 at 16:42
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred a change since when? The previous day?
– forpas
Nov 19 '18 at 16:43
Tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 16:35
Tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 16:35
SQL is a language, you need to tag your question with DBMS your using.eg: SQL Server, MySQL, SQLite ...– Sami
Nov 19 '18 at 16:38
SQL is a language, you need to tag your question with DBMS your using.eg: SQL Server, MySQL, SQLite ...– Sami
Nov 19 '18 at 16:38
Apologies, I've added the tag now.
– benjiiiii
Nov 19 '18 at 16:40
Apologies, I've added the tag now.
– benjiiiii
Nov 19 '18 at 16:40
You can use
LAG() to get the prev data and then compare it with the current data. PrevData <> CurrentData– Sami
Nov 19 '18 at 16:42
You can use
LAG() to get the prev data and then compare it with the current data. PrevData <> CurrentData– Sami
Nov 19 '18 at 16:42
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred a change since when? The previous day?
– forpas
Nov 19 '18 at 16:43
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred a change since when? The previous day?
– forpas
Nov 19 '18 at 16:43
|
show 7 more comments
4 Answers
4
active
oldest
votes
1
select p1.date_entered,
p1.WholeSale,
p1.Volume,
p1.Clearance
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 *
FROM pricetable p2
where p1.product = p2.product
and p2.date_entered < p1.date_entered
order by p2.date_entered desc) CA
where p1.product = 'TANGO'
and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc
answered Nov 19 '18 at 16:48
Cato
2,525210
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
add a comment |
1
THE QUESTION WAS ORIGINALLY TAGGED SQL-SERVER (with no version). This works for SQL Server 2012+:
Use lag(). Something like this:
select pt.*
from (select pt.*,
lag(wholesale) over (partition by product
order by date_entered) as prev_wholesale,
lag(volume) over (partition by product
order by date_entered) as prev_volume,
lag(clearance) over (partition by clearance
order by date_entered) as prev_clearance
from pricetable
where product = 'TANGO'
) pt
where (prev_wholesale is null or prev_wholesale <> wholesale) or
(prev_volume is null or prev_volume <> volume) or
(prev_clearance is null or prev_clearance <> clearance)
order by date_entered desc;
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
add a comment |
0
Compare every row's values to the previous day's values:
SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND
(
(p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
)
answered Nov 19 '18 at 17:00
forpas
9,1021421
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
add a comment |
0
You might be able to use ROW_NUMBER() to determine previous rows:
WITH cte AS (
SELECT product, date_entered, wholesale, volume, clearance, ROW_NUMBER() OVER (PARTITION BY product ORDER BY date_entered) AS rn
FROM pricetable
)
SELECT main.*
FROM cte AS main
LEFT JOIN cte AS prev ON prev.product = main.product AND prev.rn = main.rn - 1
WHERE main.rn = 1
OR prev.wholesale <> main.wholesale
OR prev.volume <> main.volume
OR prev.clearance <> main.clearance
answered Nov 19 '18 at 18:59
Salman A
176k66336424
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379016%2fselecting-rows-where-a-value-has-changed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
select p1.date_entered,
p1.WholeSale,
p1.Volume,
p1.Clearance
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 *
FROM pricetable p2
where p1.product = p2.product
and p2.date_entered < p1.date_entered
order by p2.date_entered desc) CA
where p1.product = 'TANGO'
and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc
answered Nov 19 '18 at 16:48
Cato
2,525210
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
add a comment |
1
select p1.date_entered,
p1.WholeSale,
p1.Volume,
p1.Clearance
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 *
FROM pricetable p2
where p1.product = p2.product
and p2.date_entered < p1.date_entered
order by p2.date_entered desc) CA
where p1.product = 'TANGO'
and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc
answered Nov 19 '18 at 16:48
Cato
2,525210
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
add a comment |
1
1
1
select p1.date_entered,
p1.WholeSale,
p1.Volume,
p1.Clearance
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 *
FROM pricetable p2
where p1.product = p2.product
and p2.date_entered < p1.date_entered
order by p2.date_entered desc) CA
where p1.product = 'TANGO'
and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc
answered Nov 19 '18 at 16:48
Cato
2,525210
select p1.date_entered,
p1.WholeSale,
p1.Volume,
p1.Clearance
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 *
FROM pricetable p2
where p1.product = p2.product
and p2.date_entered < p1.date_entered
order by p2.date_entered desc) CA
where p1.product = 'TANGO'
and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc
answered Nov 19 '18 at 16:48
Cato
2,525210
answered Nov 19 '18 at 16:48
Cato
2,525210
answered Nov 19 '18 at 16:48
Cato
2,525210
answered Nov 19 '18 at 16:48
Cato
2,525210
2,525210
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
add a comment |
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
Hi @Cato this works thankyou! It takes a while to run, are there any noticeable parts where the speed could be increased?
– benjiiiii
Nov 20 '18 at 9:00
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
does pricetable have an index? If you add an index where the order is product then date entered, un-clustered, probably a unique index
– Cato
Nov 20 '18 at 9:52
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Pricetable has a unique id called id yes.
– benjiiiii
Nov 20 '18 at 9:53
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
Hi, sorry I understand what you meant now. Spoke to a colleague and they explained it. It's now running almost instantly with that in place. Thanks for the help.
– benjiiiii
Nov 20 '18 at 10:08
add a comment |
1
THE QUESTION WAS ORIGINALLY TAGGED SQL-SERVER (with no version). This works for SQL Server 2012+:
Use lag(). Something like this:
select pt.*
from (select pt.*,
lag(wholesale) over (partition by product
order by date_entered) as prev_wholesale,
lag(volume) over (partition by product
order by date_entered) as prev_volume,
lag(clearance) over (partition by clearance
order by date_entered) as prev_clearance
from pricetable
where product = 'TANGO'
) pt
where (prev_wholesale is null or prev_wholesale <> wholesale) or
(prev_volume is null or prev_volume <> volume) or
(prev_clearance is null or prev_clearance <> clearance)
order by date_entered desc;
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
add a comment |
1
THE QUESTION WAS ORIGINALLY TAGGED SQL-SERVER (with no version). This works for SQL Server 2012+:
Use lag(). Something like this:
select pt.*
from (select pt.*,
lag(wholesale) over (partition by product
order by date_entered) as prev_wholesale,
lag(volume) over (partition by product
order by date_entered) as prev_volume,
lag(clearance) over (partition by clearance
order by date_entered) as prev_clearance
from pricetable
where product = 'TANGO'
) pt
where (prev_wholesale is null or prev_wholesale <> wholesale) or
(prev_volume is null or prev_volume <> volume) or
(prev_clearance is null or prev_clearance <> clearance)
order by date_entered desc;
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
add a comment |
1
1
1
THE QUESTION WAS ORIGINALLY TAGGED SQL-SERVER (with no version). This works for SQL Server 2012+:
Use lag(). Something like this:
select pt.*
from (select pt.*,
lag(wholesale) over (partition by product
order by date_entered) as prev_wholesale,
lag(volume) over (partition by product
order by date_entered) as prev_volume,
lag(clearance) over (partition by clearance
order by date_entered) as prev_clearance
from pricetable
where product = 'TANGO'
) pt
where (prev_wholesale is null or prev_wholesale <> wholesale) or
(prev_volume is null or prev_volume <> volume) or
(prev_clearance is null or prev_clearance <> clearance)
order by date_entered desc;
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
THE QUESTION WAS ORIGINALLY TAGGED SQL-SERVER (with no version). This works for SQL Server 2012+:
Use lag(). Something like this:
select pt.*
from (select pt.*,
lag(wholesale) over (partition by product
order by date_entered) as prev_wholesale,
lag(volume) over (partition by product
order by date_entered) as prev_volume,
lag(clearance) over (partition by clearance
order by date_entered) as prev_clearance
from pricetable
where product = 'TANGO'
) pt
where (prev_wholesale is null or prev_wholesale <> wholesale) or
(prev_volume is null or prev_volume <> volume) or
(prev_clearance is null or prev_clearance <> clearance)
order by date_entered desc;
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
edited Nov 19 '18 at 19:42
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
answered Nov 19 '18 at 16:36
Gordon Linoff
760k35294399
760k35294399
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
add a comment |
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I'm getting an error saying LAG() isn't a recognized built in function. When I hover over it, it appears that is is as it know it returns an int. However, when trying to execute I get the error.
– benjiiiii
Nov 19 '18 at 16:45
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
I don't think lag was supported in version 2008. There work arounds. Just search on 2008 lag.
– paparazzo
Nov 19 '18 at 17:02
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
My apologies for not tagging the version, and thanks for the answer.
– benjiiiii
Nov 20 '18 at 10:09
add a comment |
0
Compare every row's values to the previous day's values:
SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND
(
(p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
)
answered Nov 19 '18 at 17:00
forpas
9,1021421
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
add a comment |
0
Compare every row's values to the previous day's values:
SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND
(
(p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
)
answered Nov 19 '18 at 17:00
forpas
9,1021421
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
add a comment |
0
0
0
Compare every row's values to the previous day's values:
SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND
(
(p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
)
answered Nov 19 '18 at 17:00
forpas
9,1021421
Compare every row's values to the previous day's values:
SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND
(
(p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
)
answered Nov 19 '18 at 17:00
forpas
9,1021421
answered Nov 19 '18 at 17:00
forpas
9,1021421
answered Nov 19 '18 at 17:00
forpas
9,1021421
answered Nov 19 '18 at 17:00
forpas
9,1021421
9,1021421
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
add a comment |
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
Hi @forpas, this works it's just extremely slow. I'll try speed it up
– benjiiiii
Nov 20 '18 at 8:57
add a comment |
0
You might be able to use ROW_NUMBER() to determine previous rows:
WITH cte AS (
SELECT product, date_entered, wholesale, volume, clearance, ROW_NUMBER() OVER (PARTITION BY product ORDER BY date_entered) AS rn
FROM pricetable
)
SELECT main.*
FROM cte AS main
LEFT JOIN cte AS prev ON prev.product = main.product AND prev.rn = main.rn - 1
WHERE main.rn = 1
OR prev.wholesale <> main.wholesale
OR prev.volume <> main.volume
OR prev.clearance <> main.clearance
answered Nov 19 '18 at 18:59
Salman A
176k66336424
add a comment |
0
You might be able to use ROW_NUMBER() to determine previous rows:
WITH cte AS (
SELECT product, date_entered, wholesale, volume, clearance, ROW_NUMBER() OVER (PARTITION BY product ORDER BY date_entered) AS rn
FROM pricetable
)
SELECT main.*
FROM cte AS main
LEFT JOIN cte AS prev ON prev.product = main.product AND prev.rn = main.rn - 1
WHERE main.rn = 1
OR prev.wholesale <> main.wholesale
OR prev.volume <> main.volume
OR prev.clearance <> main.clearance
answered Nov 19 '18 at 18:59
Salman A
176k66336424
add a comment |
0
0
0
You might be able to use ROW_NUMBER() to determine previous rows:
WITH cte AS (
SELECT product, date_entered, wholesale, volume, clearance, ROW_NUMBER() OVER (PARTITION BY product ORDER BY date_entered) AS rn
FROM pricetable
)
SELECT main.*
FROM cte AS main
LEFT JOIN cte AS prev ON prev.product = main.product AND prev.rn = main.rn - 1
WHERE main.rn = 1
OR prev.wholesale <> main.wholesale
OR prev.volume <> main.volume
OR prev.clearance <> main.clearance
answered Nov 19 '18 at 18:59
Salman A
176k66336424
You might be able to use ROW_NUMBER() to determine previous rows:
WITH cte AS (
SELECT product, date_entered, wholesale, volume, clearance, ROW_NUMBER() OVER (PARTITION BY product ORDER BY date_entered) AS rn
FROM pricetable
)
SELECT main.*
FROM cte AS main
LEFT JOIN cte AS prev ON prev.product = main.product AND prev.rn = main.rn - 1
WHERE main.rn = 1
OR prev.wholesale <> main.wholesale
OR prev.volume <> main.volume
OR prev.clearance <> main.clearance
answered Nov 19 '18 at 18:59
Salman A
176k66336424
edited Nov 19 '18 at 19:06
answered Nov 19 '18 at 18:59
Salman A
176k66336424
answered Nov 19 '18 at 18:59
Salman A
176k66336424
answered Nov 19 '18 at 18:59
Salman A
176k66336424
176k66336424
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379016%2fselecting-rows-where-a-value-has-changed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 16:35
SQLis a language, you need to tag your question with DBMS your using.eg: SQL Server, MySQL, SQLite ...– Sami
Nov 19 '18 at 16:38
Apologies, I've added the tag now.
– benjiiiii
Nov 19 '18 at 16:40
You can use
LAG()to get the prev data and then compare it with the current data. PrevData <> CurrentData– Sami
Nov 19 '18 at 16:42
How can I make it so only rows where a change to, wholesale or volume or clearance has occurred a change since when? The previous day?
– forpas
Nov 19 '18 at 16:43