Strange Problem ???
-
Hi guys,
My code :
Select A.price1, B.price2 from(SELECT Top 100 tarih, coin, MIN(price) AS price1
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) A,(SELECT Top 100 tarih, coin, MIN(price) AS price2
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) BNormally the queries are the same, the results should be the same,
All the values of A are the same. In B, there are different values (B)
What is the reason for this and what should be the question?
-
This is not a problem at all. It's doing exactly what you are telling it to do.
You have create a full join. Every row in A will be joined to every row in B.
In the example you gave, the first row of A contains the value of 0.00005644. That row is being joined to every single row in B.
Scroll down, you will see A display 0.00017480, again joined to every row in BWhat exactly are you trying to accomplish?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Not sure what your problem is here. I see a CROSS JOIN (Cartesian Product) between two derived tables, but nothing saying what may be wrong.
-
What should I do if two or more rows contain the same values?
Normally my guess is that the code I wrote does not have to be the same?
- xtrmus2319 - Wednesday, January 24, 2018 2:42 PM
Can't answer this question without know what it is you are trying to accomplish.
-
Are you expect row 1 to have the same value in both columns, row 2 to have the same value for both columns, row 3 to have...? That's not what the SQL you've provided there is doing, that's why.
In very simplistic terms, you've effectively written a query like this:
CREATE TABLE #S (i int);INSERT INTO #SVALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);--Your querySELECT A.i, B.iFROM #S A, #S B;DROP TABLE #S;Notice that you get 100 rows from this query, as every row is joined with every row.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Normally i want to Divide a.price/b.price with this code. Because I want to see the next change in 3 minutes.
Select a.tarih,a.coin,(A.price),b.tarih,b.coin,(B.price) from (SELECT tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin OFFSET 300 ROWS
FETCH NEXT 100 ROWS ONLY) A,
(SELECT Top 100 tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) B - xtrmus2319 - Wednesday, January 24, 2018 2:55 PM
What is your question here though? You haven't yet explained the result set you were expecting either. Could you try to elaborate please?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
My question;
Create a new column. In this column I would like to show the ratio of A.Price / B.Price when 3 minutes before(a.price) and current data(B.price) with same coin.ADA/BTC A.price/B.price
ADX/BTC A.price/B.price
...
...
...
ZRT/BTC A.price/B.priceThanks.Is it understandable?
- xtrmus2319 - Wednesday, January 24, 2018 3:15 PM
Yes but what you're trying really just isn't the way SQL works.
A) As has been said you've created a cartesian join which means that every row in each result set will be joined together., that's why your result set has 10000 rows and many values where the coins don't match.
B) To generate the result sets you expect/describe would require some very specific assumptions and will be either missing data or just wrong if those assumptions aren't true
C) You're assuming data will come in exact chunks of 100 and have exactly 1 batch per minute to generate you're expected resulting offset of 100 rows current vs. 3 minutes ago and the coins in each batch will always be the same
D) Even if the above is true the query could only be run while no data is being addedWhat is the purpose of this query? Do you need to compare the value of a coin vs the same coin from exactly three minutes ago? Do you get coins that don't meet any of the assumptions above?
-
I need to compare the value of a coin vs the same coin from exactly three minutes ago. Yes 🙂
All data must meet all the values.Is there a way to do this?
- xtrmus2319 - Wednesday, January 24, 2018 11:14 PM
Can you supply some sample data and DDL please? Have a look in my signature on how to psot this.
I'm thinking this might more easily be possible using LEAD/LAG.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, January 25, 2018 2:22 AM
I get 100 different coins every minute
I want to divide the current data with the data 3 minutes ago the same coin
Create a new column. In this column I would like to show the ratio of A.Price / B.Price when 3 minutes before(a.price) and current data(B.price) with same coin.Example
Coin New Column
ADA/BTC A.price/B.price
ADX/BTC A.price/B.price
...
...
...
ZRT/BTC A.price/B.price - xtrmus2319 - Thursday, January 25, 2018 2:38 AM
Have a look at the link in my signature. We need consumable data and DDL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, January 25, 2018 2:52 AM
consumable data and DDL ?
You need just code ? Right?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply