SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange Problem ???


Strange Problem ???

Author
Message
xtrmus2319
xtrmus2319
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 39
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) B


Normally 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?



Michael L John
Michael L John
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13033 Visits: 9114
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 B

What 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/
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)

Group: General Forum Members
Points: 239492 Visits: 40664
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
xtrmus2319
xtrmus2319
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 39
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)

Group: General Forum Members
Points: 239492 Visits: 40664
xtrmus2319 - Wednesday, January 24, 2018 2:42 PM
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?


Can't answer this question without know what it is you are trying to accomplish.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48383 Visits: 16163
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 #S
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

--Your query
SELECT A.i, B.i
FROM #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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
xtrmus2319
xtrmus2319
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 39
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



Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48383 Visits: 16163
xtrmus2319 - Wednesday, January 24, 2018 2:55 PM
Normally i want to Divide a.price/b.price with this code. Because I want to see the next change in 3 minutes.

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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
xtrmus2319
xtrmus2319
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 39
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.price

Thanks.Is it understandable?
ZZartin
ZZartin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13987 Visits: 14590
xtrmus2319 - Wednesday, January 24, 2018 3:15 PM
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.price

Thanks.Is it understandable?


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 added

What 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search