How do I compare 2 rows from one table in Rapid SQL
0
I have the below query that pull all required data that I need.
SELECT a.Name, a.CPN a.Date a.Price a.dt_jay
FROM jatable a
where a.dt_jay = '11/19/2018' -- Previous day
and a.cd_type ='MKT'
and a.cpn between 2.0 and 7.5
and a.Name in ('A','B')
Order by a.CPN
My results are:
Name CPN Date Price
A 2.5 12/13/2018 102.1132813
A 2.5 3/13/2019 101.9804688
A 2.5 2/13/2019 102.3828125
A 2.5 1/14/2019 102.2460938
A 3 12/13/2018 104.2929688
A 3 3/13/2019 104.1445313
A 3 2/13/2019 104.0117188
A 3 1/14/2019 103.8789063
A 3.5 12/13/2018 105.7226563
A 3.5 3/13/2019 106.0976563
A 3.5 2/13/2019 105.9726563
A 3.5 1/14/2019 105.8476563
A 4 12/13/2018 106.7773438
A 4 3/13/2019 106.6523438
A 4 2/13/2019 106.7148438
A 4 1/14/2019 106.5898438
A 4.5 12/13/2018 106.7148438
A 4.5 3/13/2019 106.7773438
A 4.5 2/13/2019 106.5898438
A 4.5 1/14/2019 106.6523438
B 2.5 12/13/2018 87.609375
B 2.5 3/13/2019 86.953125
B 2.5 2/13/2019 87.1875
B 2.5 1/14/2019 87.40625
B 3 12/13/2018 90.7265625
B 3 3/13/2019 90.9609375
B 3 2/13/2019 91.1796875
B 3 1/14/2019 91.3828125
B 3.5 12/13/2018 94.5703125
B 3.5 3/13/2019 94.65234375
B 3.5 2/13/2019 94.4140625
B 3.5 1/14/2019 94.4921875
B 4 12/13/2018 97.3203125
B 4 3/13/2019 97.234375
B 4 2/13/2019 97.1484375
B 4 1/14/2019 97.41015625
B 4.5 12/13/2018 99.6953125
B 4.5 3/13/2019 100.0273438
B 4.5 2/13/2019 99.9140625
B 4.5 1/14/2019 99.8046875
I would like to my final results to look like this
Name CPN Date Price Name CPN Date Price Price A - Pric B
A 2.5 12/13/2018 102.1132813 B 2.5 12/13/2018 87.609375 14.50390625
A 2.5 3/13/2019 101.9804688 B 2.5 3/13/2019 86.953125 15.02734375
A 2.5 2/13/2019 102.3828125 B 2.5 2/13/2019 87.1875 15.1953125
A 2.5 1/14/2019 102.2460938 B 2.5 1/14/2019 87.40625 14.83984375
A 3 12/13/2018 104.2929688 B 3 12/13/2018 90.7265625 13.56640625
A 3 3/13/2019 104.1445313 B 3 3/13/2019 90.9609375 13.18359375
A 3 2/13/2019 104.0117188 B 3 2/13/2019 91.1796875 12.83203125
A 3 1/14/2019 103.8789063 B 3 1/14/2019 91.3828125 12.49609375
A 3.5 12/13/2018 105.7226563 B 3.5 12/13/2018 94.5703125 11.15234375
A 3.5 3/13/2019 106.0976563 B 3.5 3/13/2019 94.65234375 11.4453125
A 3.5 2/13/2019 105.9726563 B 3.5 2/13/2019 94.4140625 11.55859375
A 3.5 1/14/2019 105.8476563 B 3.5 1/14/2019 94.4921875 11.35546875
A 4 12/13/2018 106.7773438 B 4 12/13/2018 97.3203125 9.45703125
A 4 3/13/2019 106.6523438 B 4 3/13/2019 97.234375 9.41796875
A 4 2/13/2019 106.7148438 B 4 2/13/2019 97.1484375 9.56640625
A 4 1/14/2019 106.5898438 B 4 1/14/2019 97.41015625 9.1796875
A 4.5 12/13/2018 106.7148438 B 4.5 12/13/2018 99.6953125 7.01953125
A 4.5 3/13/2019 106.7773438 B 4.5 3/13/2019 100.0273438 6.75
A 4.5 2/13/2019 106.5898438 B 4.5 2/13/2019 99.9140625 6.67578125
A 4.5 1/14/2019 106.6523438 B 4.5 1/14/2019 99.8046875 6.84765625
I would like to compare A with B from the name column as shown above. Matching the data with Date and CPN then subtracting the price columns (PRICE A - Price B)
Is this possible?
sql
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
asked Nov 20 at 16:32
Jay
61
add a comment |
0
I have the below query that pull all required data that I need.
SELECT a.Name, a.CPN a.Date a.Price a.dt_jay
FROM jatable a
where a.dt_jay = '11/19/2018' -- Previous day
and a.cd_type ='MKT'
and a.cpn between 2.0 and 7.5
and a.Name in ('A','B')
Order by a.CPN
My results are:
Name CPN Date Price
A 2.5 12/13/2018 102.1132813
A 2.5 3/13/2019 101.9804688
A 2.5 2/13/2019 102.3828125
A 2.5 1/14/2019 102.2460938
A 3 12/13/2018 104.2929688
A 3 3/13/2019 104.1445313
A 3 2/13/2019 104.0117188
A 3 1/14/2019 103.8789063
A 3.5 12/13/2018 105.7226563
A 3.5 3/13/2019 106.0976563
A 3.5 2/13/2019 105.9726563
A 3.5 1/14/2019 105.8476563
A 4 12/13/2018 106.7773438
A 4 3/13/2019 106.6523438
A 4 2/13/2019 106.7148438
A 4 1/14/2019 106.5898438
A 4.5 12/13/2018 106.7148438
A 4.5 3/13/2019 106.7773438
A 4.5 2/13/2019 106.5898438
A 4.5 1/14/2019 106.6523438
B 2.5 12/13/2018 87.609375
B 2.5 3/13/2019 86.953125
B 2.5 2/13/2019 87.1875
B 2.5 1/14/2019 87.40625
B 3 12/13/2018 90.7265625
B 3 3/13/2019 90.9609375
B 3 2/13/2019 91.1796875
B 3 1/14/2019 91.3828125
B 3.5 12/13/2018 94.5703125
B 3.5 3/13/2019 94.65234375
B 3.5 2/13/2019 94.4140625
B 3.5 1/14/2019 94.4921875
B 4 12/13/2018 97.3203125
B 4 3/13/2019 97.234375
B 4 2/13/2019 97.1484375
B 4 1/14/2019 97.41015625
B 4.5 12/13/2018 99.6953125
B 4.5 3/13/2019 100.0273438
B 4.5 2/13/2019 99.9140625
B 4.5 1/14/2019 99.8046875
I would like to my final results to look like this
Name CPN Date Price Name CPN Date Price Price A - Pric B
A 2.5 12/13/2018 102.1132813 B 2.5 12/13/2018 87.609375 14.50390625
A 2.5 3/13/2019 101.9804688 B 2.5 3/13/2019 86.953125 15.02734375
A 2.5 2/13/2019 102.3828125 B 2.5 2/13/2019 87.1875 15.1953125
A 2.5 1/14/2019 102.2460938 B 2.5 1/14/2019 87.40625 14.83984375
A 3 12/13/2018 104.2929688 B 3 12/13/2018 90.7265625 13.56640625
A 3 3/13/2019 104.1445313 B 3 3/13/2019 90.9609375 13.18359375
A 3 2/13/2019 104.0117188 B 3 2/13/2019 91.1796875 12.83203125
A 3 1/14/2019 103.8789063 B 3 1/14/2019 91.3828125 12.49609375
A 3.5 12/13/2018 105.7226563 B 3.5 12/13/2018 94.5703125 11.15234375
A 3.5 3/13/2019 106.0976563 B 3.5 3/13/2019 94.65234375 11.4453125
A 3.5 2/13/2019 105.9726563 B 3.5 2/13/2019 94.4140625 11.55859375
A 3.5 1/14/2019 105.8476563 B 3.5 1/14/2019 94.4921875 11.35546875
A 4 12/13/2018 106.7773438 B 4 12/13/2018 97.3203125 9.45703125
A 4 3/13/2019 106.6523438 B 4 3/13/2019 97.234375 9.41796875
A 4 2/13/2019 106.7148438 B 4 2/13/2019 97.1484375 9.56640625
A 4 1/14/2019 106.5898438 B 4 1/14/2019 97.41015625 9.1796875
A 4.5 12/13/2018 106.7148438 B 4.5 12/13/2018 99.6953125 7.01953125
A 4.5 3/13/2019 106.7773438 B 4.5 3/13/2019 100.0273438 6.75
A 4.5 2/13/2019 106.5898438 B 4.5 2/13/2019 99.9140625 6.67578125
A 4.5 1/14/2019 106.6523438 B 4.5 1/14/2019 99.8046875 6.84765625
I would like to compare A with B from the name column as shown above. Matching the data with Date and CPN then subtracting the price columns (PRICE A - Price B)
Is this possible?
sql
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
asked Nov 20 at 16:32
Jay
61
add a comment |
0
0
0
I have the below query that pull all required data that I need.
SELECT a.Name, a.CPN a.Date a.Price a.dt_jay
FROM jatable a
where a.dt_jay = '11/19/2018' -- Previous day
and a.cd_type ='MKT'
and a.cpn between 2.0 and 7.5
and a.Name in ('A','B')
Order by a.CPN
My results are:
Name CPN Date Price
A 2.5 12/13/2018 102.1132813
A 2.5 3/13/2019 101.9804688
A 2.5 2/13/2019 102.3828125
A 2.5 1/14/2019 102.2460938
A 3 12/13/2018 104.2929688
A 3 3/13/2019 104.1445313
A 3 2/13/2019 104.0117188
A 3 1/14/2019 103.8789063
A 3.5 12/13/2018 105.7226563
A 3.5 3/13/2019 106.0976563
A 3.5 2/13/2019 105.9726563
A 3.5 1/14/2019 105.8476563
A 4 12/13/2018 106.7773438
A 4 3/13/2019 106.6523438
A 4 2/13/2019 106.7148438
A 4 1/14/2019 106.5898438
A 4.5 12/13/2018 106.7148438
A 4.5 3/13/2019 106.7773438
A 4.5 2/13/2019 106.5898438
A 4.5 1/14/2019 106.6523438
B 2.5 12/13/2018 87.609375
B 2.5 3/13/2019 86.953125
B 2.5 2/13/2019 87.1875
B 2.5 1/14/2019 87.40625
B 3 12/13/2018 90.7265625
B 3 3/13/2019 90.9609375
B 3 2/13/2019 91.1796875
B 3 1/14/2019 91.3828125
B 3.5 12/13/2018 94.5703125
B 3.5 3/13/2019 94.65234375
B 3.5 2/13/2019 94.4140625
B 3.5 1/14/2019 94.4921875
B 4 12/13/2018 97.3203125
B 4 3/13/2019 97.234375
B 4 2/13/2019 97.1484375
B 4 1/14/2019 97.41015625
B 4.5 12/13/2018 99.6953125
B 4.5 3/13/2019 100.0273438
B 4.5 2/13/2019 99.9140625
B 4.5 1/14/2019 99.8046875
I would like to my final results to look like this
Name CPN Date Price Name CPN Date Price Price A - Pric B
A 2.5 12/13/2018 102.1132813 B 2.5 12/13/2018 87.609375 14.50390625
A 2.5 3/13/2019 101.9804688 B 2.5 3/13/2019 86.953125 15.02734375
A 2.5 2/13/2019 102.3828125 B 2.5 2/13/2019 87.1875 15.1953125
A 2.5 1/14/2019 102.2460938 B 2.5 1/14/2019 87.40625 14.83984375
A 3 12/13/2018 104.2929688 B 3 12/13/2018 90.7265625 13.56640625
A 3 3/13/2019 104.1445313 B 3 3/13/2019 90.9609375 13.18359375
A 3 2/13/2019 104.0117188 B 3 2/13/2019 91.1796875 12.83203125
A 3 1/14/2019 103.8789063 B 3 1/14/2019 91.3828125 12.49609375
A 3.5 12/13/2018 105.7226563 B 3.5 12/13/2018 94.5703125 11.15234375
A 3.5 3/13/2019 106.0976563 B 3.5 3/13/2019 94.65234375 11.4453125
A 3.5 2/13/2019 105.9726563 B 3.5 2/13/2019 94.4140625 11.55859375
A 3.5 1/14/2019 105.8476563 B 3.5 1/14/2019 94.4921875 11.35546875
A 4 12/13/2018 106.7773438 B 4 12/13/2018 97.3203125 9.45703125
A 4 3/13/2019 106.6523438 B 4 3/13/2019 97.234375 9.41796875
A 4 2/13/2019 106.7148438 B 4 2/13/2019 97.1484375 9.56640625
A 4 1/14/2019 106.5898438 B 4 1/14/2019 97.41015625 9.1796875
A 4.5 12/13/2018 106.7148438 B 4.5 12/13/2018 99.6953125 7.01953125
A 4.5 3/13/2019 106.7773438 B 4.5 3/13/2019 100.0273438 6.75
A 4.5 2/13/2019 106.5898438 B 4.5 2/13/2019 99.9140625 6.67578125
A 4.5 1/14/2019 106.6523438 B 4.5 1/14/2019 99.8046875 6.84765625
I would like to compare A with B from the name column as shown above. Matching the data with Date and CPN then subtracting the price columns (PRICE A - Price B)
Is this possible?
sql
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
asked Nov 20 at 16:32
Jay
61
I have the below query that pull all required data that I need.
SELECT a.Name, a.CPN a.Date a.Price a.dt_jay
FROM jatable a
where a.dt_jay = '11/19/2018' -- Previous day
and a.cd_type ='MKT'
and a.cpn between 2.0 and 7.5
and a.Name in ('A','B')
Order by a.CPN
My results are:
Name CPN Date Price
A 2.5 12/13/2018 102.1132813
A 2.5 3/13/2019 101.9804688
A 2.5 2/13/2019 102.3828125
A 2.5 1/14/2019 102.2460938
A 3 12/13/2018 104.2929688
A 3 3/13/2019 104.1445313
A 3 2/13/2019 104.0117188
A 3 1/14/2019 103.8789063
A 3.5 12/13/2018 105.7226563
A 3.5 3/13/2019 106.0976563
A 3.5 2/13/2019 105.9726563
A 3.5 1/14/2019 105.8476563
A 4 12/13/2018 106.7773438
A 4 3/13/2019 106.6523438
A 4 2/13/2019 106.7148438
A 4 1/14/2019 106.5898438
A 4.5 12/13/2018 106.7148438
A 4.5 3/13/2019 106.7773438
A 4.5 2/13/2019 106.5898438
A 4.5 1/14/2019 106.6523438
B 2.5 12/13/2018 87.609375
B 2.5 3/13/2019 86.953125
B 2.5 2/13/2019 87.1875
B 2.5 1/14/2019 87.40625
B 3 12/13/2018 90.7265625
B 3 3/13/2019 90.9609375
B 3 2/13/2019 91.1796875
B 3 1/14/2019 91.3828125
B 3.5 12/13/2018 94.5703125
B 3.5 3/13/2019 94.65234375
B 3.5 2/13/2019 94.4140625
B 3.5 1/14/2019 94.4921875
B 4 12/13/2018 97.3203125
B 4 3/13/2019 97.234375
B 4 2/13/2019 97.1484375
B 4 1/14/2019 97.41015625
B 4.5 12/13/2018 99.6953125
B 4.5 3/13/2019 100.0273438
B 4.5 2/13/2019 99.9140625
B 4.5 1/14/2019 99.8046875
I would like to my final results to look like this
Name CPN Date Price Name CPN Date Price Price A - Pric B
A 2.5 12/13/2018 102.1132813 B 2.5 12/13/2018 87.609375 14.50390625
A 2.5 3/13/2019 101.9804688 B 2.5 3/13/2019 86.953125 15.02734375
A 2.5 2/13/2019 102.3828125 B 2.5 2/13/2019 87.1875 15.1953125
A 2.5 1/14/2019 102.2460938 B 2.5 1/14/2019 87.40625 14.83984375
A 3 12/13/2018 104.2929688 B 3 12/13/2018 90.7265625 13.56640625
A 3 3/13/2019 104.1445313 B 3 3/13/2019 90.9609375 13.18359375
A 3 2/13/2019 104.0117188 B 3 2/13/2019 91.1796875 12.83203125
A 3 1/14/2019 103.8789063 B 3 1/14/2019 91.3828125 12.49609375
A 3.5 12/13/2018 105.7226563 B 3.5 12/13/2018 94.5703125 11.15234375
A 3.5 3/13/2019 106.0976563 B 3.5 3/13/2019 94.65234375 11.4453125
A 3.5 2/13/2019 105.9726563 B 3.5 2/13/2019 94.4140625 11.55859375
A 3.5 1/14/2019 105.8476563 B 3.5 1/14/2019 94.4921875 11.35546875
A 4 12/13/2018 106.7773438 B 4 12/13/2018 97.3203125 9.45703125
A 4 3/13/2019 106.6523438 B 4 3/13/2019 97.234375 9.41796875
A 4 2/13/2019 106.7148438 B 4 2/13/2019 97.1484375 9.56640625
A 4 1/14/2019 106.5898438 B 4 1/14/2019 97.41015625 9.1796875
A 4.5 12/13/2018 106.7148438 B 4.5 12/13/2018 99.6953125 7.01953125
A 4.5 3/13/2019 106.7773438 B 4.5 3/13/2019 100.0273438 6.75
A 4.5 2/13/2019 106.5898438 B 4.5 2/13/2019 99.9140625 6.67578125
A 4.5 1/14/2019 106.6523438 B 4.5 1/14/2019 99.8046875 6.84765625
I would like to compare A with B from the name column as shown above. Matching the data with Date and CPN then subtracting the price columns (PRICE A - Price B)
Is this possible?
sql
sql
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
asked Nov 20 at 16:32
Jay
61
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
asked Nov 20 at 16:32
Jay
61
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
edited Nov 20 at 20:34
a_horse_with_no_name
291k46444538
291k46444538
asked Nov 20 at 16:32
Jay
61
asked Nov 20 at 16:32
Jay
61
asked Nov 20 at 16:32
Jay
61
61
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You can use conditional aggregation:
select 'A' as name,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'A' then a.date end) as a_date,
max(case when a.Name = 'A' then a.price end) as a_price,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'B' then a.date end) as b_date,
max(case when a.Name = 'B' then a.price end) as b_price,
max(case when a.Name = 'B' then a.CPN end) as b_cpn
from jatable a
where a.dt_jay = '11/19/2018' and -- Should use YYYY-MM-DD format
a.cd_type = 'MKT' and
a.cpn between 2.0 and 7.5 and
a.Name in ('A', 'B')
group by date;
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
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%2f53397455%2fhow-do-i-compare-2-rows-from-one-table-in-rapid-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can use conditional aggregation:
select 'A' as name,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'A' then a.date end) as a_date,
max(case when a.Name = 'A' then a.price end) as a_price,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'B' then a.date end) as b_date,
max(case when a.Name = 'B' then a.price end) as b_price,
max(case when a.Name = 'B' then a.CPN end) as b_cpn
from jatable a
where a.dt_jay = '11/19/2018' and -- Should use YYYY-MM-DD format
a.cd_type = 'MKT' and
a.cpn between 2.0 and 7.5 and
a.Name in ('A', 'B')
group by date;
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
add a comment |
0
You can use conditional aggregation:
select 'A' as name,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'A' then a.date end) as a_date,
max(case when a.Name = 'A' then a.price end) as a_price,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'B' then a.date end) as b_date,
max(case when a.Name = 'B' then a.price end) as b_price,
max(case when a.Name = 'B' then a.CPN end) as b_cpn
from jatable a
where a.dt_jay = '11/19/2018' and -- Should use YYYY-MM-DD format
a.cd_type = 'MKT' and
a.cpn between 2.0 and 7.5 and
a.Name in ('A', 'B')
group by date;
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
add a comment |
0
0
0
You can use conditional aggregation:
select 'A' as name,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'A' then a.date end) as a_date,
max(case when a.Name = 'A' then a.price end) as a_price,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'B' then a.date end) as b_date,
max(case when a.Name = 'B' then a.price end) as b_price,
max(case when a.Name = 'B' then a.CPN end) as b_cpn
from jatable a
where a.dt_jay = '11/19/2018' and -- Should use YYYY-MM-DD format
a.cd_type = 'MKT' and
a.cpn between 2.0 and 7.5 and
a.Name in ('A', 'B')
group by date;
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
You can use conditional aggregation:
select 'A' as name,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'A' then a.date end) as a_date,
max(case when a.Name = 'A' then a.price end) as a_price,
max(case when a.Name = 'A' then a.CPN end) as a_cpn,
max(case when a.Name = 'B' then a.date end) as b_date,
max(case when a.Name = 'B' then a.price end) as b_price,
max(case when a.Name = 'B' then a.CPN end) as b_cpn
from jatable a
where a.dt_jay = '11/19/2018' and -- Should use YYYY-MM-DD format
a.cd_type = 'MKT' and
a.cpn between 2.0 and 7.5 and
a.Name in ('A', 'B')
group by date;
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
answered Nov 20 at 18:34
Gordon Linoff
757k35291399
757k35291399
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
add a comment |
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
Thanks! this worked perfectly. with one adjustment. I added cpn to the group by and added an order by cpn. What if I had more conditions to add to the name column that I wanted to compare to 'B'? For example the date from a.name column has 'A' 'C' 'D' with different attributes. How can I add them?
– Jay
Nov 20 at 19:47
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%2f53397455%2fhow-do-i-compare-2-rows-from-one-table-in-rapid-sql%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
