Mixing
SQL Server : query optimization in stored procedure
0
Please let me know how can optimize this SQL Server query:
SELECT
C.Id AS CurrencyId, C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName, C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol, C.IsActiveImport AS ActiveImport,
(SELECT TOP 1 Price
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestAsk
FROM
Currencies C
INNER JOIN
CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
sql sql-server-2016
edited Jan 2 at 6:21
marc_s
582k13011221268
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
add a comment |
0
Please let me know how can optimize this SQL Server query:
SELECT
C.Id AS CurrencyId, C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName, C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol, C.IsActiveImport AS ActiveImport,
(SELECT TOP 1 Price
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestAsk
FROM
Currencies C
INNER JOIN
CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
sql sql-server-2016
edited Jan 2 at 6:21
marc_s
582k13011221268
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
It would be nice if you also include what you have tried so far and where did you stuck? What needs to be optimized exactly? execution time, memory or something else.
– Arpit
Jan 2 at 7:04
add a comment |
0
0
0
Please let me know how can optimize this SQL Server query:
SELECT
C.Id AS CurrencyId, C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName, C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol, C.IsActiveImport AS ActiveImport,
(SELECT TOP 1 Price
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestAsk
FROM
Currencies C
INNER JOIN
CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
sql sql-server-2016
edited Jan 2 at 6:21
marc_s
582k13011221268
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
Please let me know how can optimize this SQL Server query:
SELECT
C.Id AS CurrencyId, C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName, C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol, C.IsActiveImport AS ActiveImport,
(SELECT TOP 1 Price
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk
FROM CurrencyRates
WHERE C.Id = ProductId
ORDER BY PriceDate DESC) AS BestAsk
FROM
Currencies C
INNER JOIN
CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
sql sql-server-2016
sql sql-server-2016
edited Jan 2 at 6:21
marc_s
582k13011221268
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
edited Jan 2 at 6:21
marc_s
582k13011221268
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
edited Jan 2 at 6:21
marc_s
582k13011221268
edited Jan 2 at 6:21
marc_s
582k13011221268
edited Jan 2 at 6:21
marc_s
582k13011221268
582k13011221268
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
asked Jan 2 at 5:26
Shailesh KalasariyaShailesh Kalasariya
125
125
It would be nice if you also include what you have tried so far and where did you stuck? What needs to be optimized exactly? execution time, memory or something else.
– Arpit
Jan 2 at 7:04
add a comment |
It would be nice if you also include what you have tried so far and where did you stuck? What needs to be optimized exactly? execution time, memory or something else.
– Arpit
Jan 2 at 7:04
It would be nice if you also include what you have tried so far and where did you stuck? What needs to be optimized exactly? execution time, memory or something else.
– Arpit
Jan 2 at 7:04
It would be nice if you also include what you have tried so far and where did you stuck? What needs to be optimized exactly? execution time, memory or something else.
– Arpit
Jan 2 at 7:04
add a comment |
2 Answers
2
active
oldest
votes
1
Hi Shailesh Kalasariya,
You could outer apply to combine these top subqueries into one subquery like this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
OUTER APPLY (SELECT TOP 1 Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) D
Or you could use row_number function to rewrite this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
LEFT JOIN (SELECT ROW_NUMBER() over (partition by ProductId ORDER BY PriceDate DESC) as RN,Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk,ProductId FROM CurrencyRates ) D ON D.RN=1 AND D.ProductId=C.Id
Best Regards,
Will
answered Jan 2 at 6:42
Will KongWill Kong
713
You're welcome.
– Will Kong
Jan 2 at 7:07
add a comment |
1
You can make use of analytical functions for first_value to get all of the data from CurrencyRates table and join it once instead of a select in a select query.
This would be useful if you are batch processing the data(ie you wish to optimize the select output for all the rows to be returned)
If you are building a app/ui screen with pagination then select in a select would be more suitable as it would be optimized for getting the first_rows first.
SELECT C.Id AS CurrencyId
, C.DisplayName AS CurrencyName
, C.TickerSymbol AS TickerSymbol
, m.DisplayName AS MarketName
, C.BaseCurrency AS BaseCurrency
, C.BaseCurrencySymbol AS CurrencySymbol
, C.IsActiveImport AS ActiveImport
/*
, (SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price
, (SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H
, (SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H
, (SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H
, (SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H
, (SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid
, (SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
, Cr.Price
, Cr.Open24H
, Cr.Volume24H
, Cr.Low24H
, Cr.High24H
, Cr.BestBid
, Cr.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M
ON C.CryptoMarketId = C.CryptoMarketId
JOIN (SELECT /*first_value(Price) over(partition by productId order by price_date desc) as price
,first_value(Open_24h) over(partition by productId order by price_date desc) as Open_24h
,first_value(Volume_24h) over(partition by productId order by price_date desc) as Volume_24h
,first_value(Low_24h) over(partition by productId order by price_date desc) as Low_24h
,first_value(High_24h) over(partition by productId order by price_date desc) as High_24h
,first_value(BestBid) over(partition by productId order by price_date desc) as BestBid
,first_value(BestAsk) over(partition by productId order by price_date desc) as BestAsk
*/
, Price
, Open24H
, Volume24H
, Low24H
, High24H
, BestBid
, BestAsk
,row_number() over(partition by productId order by price_date desc) as rnk
,productId
FROM CurrencyRates
)Cr
ON C.Id=Cr.productId
AND Cr.rnk=1
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
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%2f54001621%2fsql-server-query-optimization-in-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Hi Shailesh Kalasariya,
You could outer apply to combine these top subqueries into one subquery like this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
OUTER APPLY (SELECT TOP 1 Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) D
Or you could use row_number function to rewrite this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
LEFT JOIN (SELECT ROW_NUMBER() over (partition by ProductId ORDER BY PriceDate DESC) as RN,Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk,ProductId FROM CurrencyRates ) D ON D.RN=1 AND D.ProductId=C.Id
Best Regards,
Will
answered Jan 2 at 6:42
Will KongWill Kong
713
You're welcome.
– Will Kong
Jan 2 at 7:07
add a comment |
1
Hi Shailesh Kalasariya,
You could outer apply to combine these top subqueries into one subquery like this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
OUTER APPLY (SELECT TOP 1 Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) D
Or you could use row_number function to rewrite this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
LEFT JOIN (SELECT ROW_NUMBER() over (partition by ProductId ORDER BY PriceDate DESC) as RN,Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk,ProductId FROM CurrencyRates ) D ON D.RN=1 AND D.ProductId=C.Id
Best Regards,
Will
answered Jan 2 at 6:42
Will KongWill Kong
713
You're welcome.
– Will Kong
Jan 2 at 7:07
add a comment |
1
1
1
Hi Shailesh Kalasariya,
You could outer apply to combine these top subqueries into one subquery like this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
OUTER APPLY (SELECT TOP 1 Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) D
Or you could use row_number function to rewrite this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
LEFT JOIN (SELECT ROW_NUMBER() over (partition by ProductId ORDER BY PriceDate DESC) as RN,Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk,ProductId FROM CurrencyRates ) D ON D.RN=1 AND D.ProductId=C.Id
Best Regards,
Will
answered Jan 2 at 6:42
Will KongWill Kong
713
Hi Shailesh Kalasariya,
You could outer apply to combine these top subqueries into one subquery like this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
OUTER APPLY (SELECT TOP 1 Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) D
Or you could use row_number function to rewrite this.
SELECT
C.Id AS CurrencyId,
C.DisplayName AS CurrencyName,
C.TickerSymbol AS TickerSymbol,
m.DisplayName AS MarketName,
C.BaseCurrency AS BaseCurrency,
C.BaseCurrencySymbol AS CurrencySymbol,
C.IsActiveImport AS ActiveImport,
/*
(SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price,
(SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H,
(SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H,
(SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H,
(SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H,
(SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid,
(SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
D.Price,
D.Open_24h,
D.Volume_24h,
D.Low_24h,
D.High_24h,
D.BestBid,
D.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId
LEFT JOIN (SELECT ROW_NUMBER() over (partition by ProductId ORDER BY PriceDate DESC) as RN,Price,Open_24h,Volume_24h,Low_24h,High_24h,BestBid,BestAsk,ProductId FROM CurrencyRates ) D ON D.RN=1 AND D.ProductId=C.Id
Best Regards,
Will
answered Jan 2 at 6:42
Will KongWill Kong
713
answered Jan 2 at 6:42
Will KongWill Kong
713
answered Jan 2 at 6:42
Will KongWill Kong
713
answered Jan 2 at 6:42
Will KongWill Kong
713
713
You're welcome.
– Will Kong
Jan 2 at 7:07
add a comment |
You're welcome.
– Will Kong
Jan 2 at 7:07
You're welcome.
– Will Kong
Jan 2 at 7:07
You're welcome.
– Will Kong
Jan 2 at 7:07
add a comment |
1
You can make use of analytical functions for first_value to get all of the data from CurrencyRates table and join it once instead of a select in a select query.
This would be useful if you are batch processing the data(ie you wish to optimize the select output for all the rows to be returned)
If you are building a app/ui screen with pagination then select in a select would be more suitable as it would be optimized for getting the first_rows first.
SELECT C.Id AS CurrencyId
, C.DisplayName AS CurrencyName
, C.TickerSymbol AS TickerSymbol
, m.DisplayName AS MarketName
, C.BaseCurrency AS BaseCurrency
, C.BaseCurrencySymbol AS CurrencySymbol
, C.IsActiveImport AS ActiveImport
/*
, (SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price
, (SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H
, (SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H
, (SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H
, (SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H
, (SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid
, (SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
, Cr.Price
, Cr.Open24H
, Cr.Volume24H
, Cr.Low24H
, Cr.High24H
, Cr.BestBid
, Cr.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M
ON C.CryptoMarketId = C.CryptoMarketId
JOIN (SELECT /*first_value(Price) over(partition by productId order by price_date desc) as price
,first_value(Open_24h) over(partition by productId order by price_date desc) as Open_24h
,first_value(Volume_24h) over(partition by productId order by price_date desc) as Volume_24h
,first_value(Low_24h) over(partition by productId order by price_date desc) as Low_24h
,first_value(High_24h) over(partition by productId order by price_date desc) as High_24h
,first_value(BestBid) over(partition by productId order by price_date desc) as BestBid
,first_value(BestAsk) over(partition by productId order by price_date desc) as BestAsk
*/
, Price
, Open24H
, Volume24H
, Low24H
, High24H
, BestBid
, BestAsk
,row_number() over(partition by productId order by price_date desc) as rnk
,productId
FROM CurrencyRates
)Cr
ON C.Id=Cr.productId
AND Cr.rnk=1
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
add a comment |
1
You can make use of analytical functions for first_value to get all of the data from CurrencyRates table and join it once instead of a select in a select query.
This would be useful if you are batch processing the data(ie you wish to optimize the select output for all the rows to be returned)
If you are building a app/ui screen with pagination then select in a select would be more suitable as it would be optimized for getting the first_rows first.
SELECT C.Id AS CurrencyId
, C.DisplayName AS CurrencyName
, C.TickerSymbol AS TickerSymbol
, m.DisplayName AS MarketName
, C.BaseCurrency AS BaseCurrency
, C.BaseCurrencySymbol AS CurrencySymbol
, C.IsActiveImport AS ActiveImport
/*
, (SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price
, (SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H
, (SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H
, (SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H
, (SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H
, (SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid
, (SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
, Cr.Price
, Cr.Open24H
, Cr.Volume24H
, Cr.Low24H
, Cr.High24H
, Cr.BestBid
, Cr.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M
ON C.CryptoMarketId = C.CryptoMarketId
JOIN (SELECT /*first_value(Price) over(partition by productId order by price_date desc) as price
,first_value(Open_24h) over(partition by productId order by price_date desc) as Open_24h
,first_value(Volume_24h) over(partition by productId order by price_date desc) as Volume_24h
,first_value(Low_24h) over(partition by productId order by price_date desc) as Low_24h
,first_value(High_24h) over(partition by productId order by price_date desc) as High_24h
,first_value(BestBid) over(partition by productId order by price_date desc) as BestBid
,first_value(BestAsk) over(partition by productId order by price_date desc) as BestAsk
*/
, Price
, Open24H
, Volume24H
, Low24H
, High24H
, BestBid
, BestAsk
,row_number() over(partition by productId order by price_date desc) as rnk
,productId
FROM CurrencyRates
)Cr
ON C.Id=Cr.productId
AND Cr.rnk=1
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
add a comment |
1
1
1
You can make use of analytical functions for first_value to get all of the data from CurrencyRates table and join it once instead of a select in a select query.
This would be useful if you are batch processing the data(ie you wish to optimize the select output for all the rows to be returned)
If you are building a app/ui screen with pagination then select in a select would be more suitable as it would be optimized for getting the first_rows first.
SELECT C.Id AS CurrencyId
, C.DisplayName AS CurrencyName
, C.TickerSymbol AS TickerSymbol
, m.DisplayName AS MarketName
, C.BaseCurrency AS BaseCurrency
, C.BaseCurrencySymbol AS CurrencySymbol
, C.IsActiveImport AS ActiveImport
/*
, (SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price
, (SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H
, (SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H
, (SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H
, (SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H
, (SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid
, (SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
, Cr.Price
, Cr.Open24H
, Cr.Volume24H
, Cr.Low24H
, Cr.High24H
, Cr.BestBid
, Cr.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M
ON C.CryptoMarketId = C.CryptoMarketId
JOIN (SELECT /*first_value(Price) over(partition by productId order by price_date desc) as price
,first_value(Open_24h) over(partition by productId order by price_date desc) as Open_24h
,first_value(Volume_24h) over(partition by productId order by price_date desc) as Volume_24h
,first_value(Low_24h) over(partition by productId order by price_date desc) as Low_24h
,first_value(High_24h) over(partition by productId order by price_date desc) as High_24h
,first_value(BestBid) over(partition by productId order by price_date desc) as BestBid
,first_value(BestAsk) over(partition by productId order by price_date desc) as BestAsk
*/
, Price
, Open24H
, Volume24H
, Low24H
, High24H
, BestBid
, BestAsk
,row_number() over(partition by productId order by price_date desc) as rnk
,productId
FROM CurrencyRates
)Cr
ON C.Id=Cr.productId
AND Cr.rnk=1
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
You can make use of analytical functions for first_value to get all of the data from CurrencyRates table and join it once instead of a select in a select query.
This would be useful if you are batch processing the data(ie you wish to optimize the select output for all the rows to be returned)
If you are building a app/ui screen with pagination then select in a select would be more suitable as it would be optimized for getting the first_rows first.
SELECT C.Id AS CurrencyId
, C.DisplayName AS CurrencyName
, C.TickerSymbol AS TickerSymbol
, m.DisplayName AS MarketName
, C.BaseCurrency AS BaseCurrency
, C.BaseCurrencySymbol AS CurrencySymbol
, C.IsActiveImport AS ActiveImport
/*
, (SELECT TOP 1 Price FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Price
, (SELECT TOP 1 Open_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Open24H
, (SELECT TOP 1 Volume_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Volume24H
, (SELECT TOP 1 Low_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS Low24H
, (SELECT TOP 1 High_24h FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS High24H
, (SELECT TOP 1 BestBid FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestBid
, (SELECT TOP 1 BestAsk FROM CurrencyRates WHERE C.Id=ProductId ORDER BY PriceDate DESC) AS BestAsk
*/
, Cr.Price
, Cr.Open24H
, Cr.Volume24H
, Cr.Low24H
, Cr.High24H
, Cr.BestBid
, Cr.BestAsk
FROM Currencies C
INNER JOIN CryptoMarketsMaster M
ON C.CryptoMarketId = C.CryptoMarketId
JOIN (SELECT /*first_value(Price) over(partition by productId order by price_date desc) as price
,first_value(Open_24h) over(partition by productId order by price_date desc) as Open_24h
,first_value(Volume_24h) over(partition by productId order by price_date desc) as Volume_24h
,first_value(Low_24h) over(partition by productId order by price_date desc) as Low_24h
,first_value(High_24h) over(partition by productId order by price_date desc) as High_24h
,first_value(BestBid) over(partition by productId order by price_date desc) as BestBid
,first_value(BestAsk) over(partition by productId order by price_date desc) as BestAsk
*/
, Price
, Open24H
, Volume24H
, Low24H
, High24H
, BestBid
, BestAsk
,row_number() over(partition by productId order by price_date desc) as rnk
,productId
FROM CurrencyRates
)Cr
ON C.Id=Cr.productId
AND Cr.rnk=1
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
edited Jan 2 at 7:17
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
answered Jan 2 at 5:37
George JosephGeorge Joseph
1,590510
1,590510
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
add a comment |
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
unable to find productid "ON C.Id=Cr.productId AND Cr.rnk=1"
– Shailesh Kalasariya
Jan 2 at 5:52
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
Have included the field in Cr block
– George Joseph
Jan 2 at 6:59
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
yeah. This is working. Thanks. I made one mistake also which is "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = C.CryptoMarketId". It should like "INNER JOIN CryptoMarketsMaster M ON C.CryptoMarketId = M.Id". I was getting duplicate records because of this mistake. Thank you very much.
– Shailesh Kalasariya
Jan 2 at 7:04
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Glad i could help. Upvote and mark as answer if you are fine with the results
– George Joseph
Jan 2 at 7:07
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
Actually the first_value analytical function is redundant, the same can be achieved using row_number only
– George Joseph
Jan 2 at 7:17
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.
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%2f54001621%2fsql-server-query-optimization-in-stored-procedure%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
It would be nice if you also include what you have tried so far and where did you stuck? What needs to be optimized exactly? execution time, memory or something else.
– Arpit
Jan 2 at 7:04