May 20, 2013 at 2:56 pm
Why does this return more then one row? The book I'm reading doesn't say why and I'm used to select top meaning it will only select that many rows.
SELECT mth,qty,
SIGN(S1.qty -
(SELECT TOP (1) qty
FROM dbo.Sales AS S2
WHERE S2.mth < S1.mth
ORDER BY S2.mth DESC)) AS sgn
FROM dbo.Sales AS S1
GO
If you need to test this is the table I'm running against.
USE tempdb
GO
CREATE TABLE dbo.Sales
(mth DATE NOT NULL PRIMARY KEY,
qty INT NOT NULL)
INSERT INTO dbo.Sales(mth,qty) VALUES
('20071201',100),
('20080101',110),
('20080201',120),
('20080301',130),
('20080401',140),
('20080501',140),
('20080601',130),
('20080701',120),
('20080801',110),
('20080901',100),
('20081001',110),
('20081101',100),
('20081201',120),
('20090101',130),
('20090201',110),
('20090301',120),
('20090401',120),
('20090501',130),
('20090601',130),
('20090701',100),
('20090801',110),
('20090901',140),
('20091001',100),
('20091101',110),
('20091201',120)
Thanks....
May 20, 2013 at 6:07 pm
I think your post is lacking clarity on what you are trying to achieve. You have your base data, now tell us what you are expecting the output to be.
- Rex
May 21, 2013 at 1:24 am
Are you pulling the top first record? No t sure if I understand clearly.
TOP (1) should be in your main table not on the derived table "sgn"
SELECT TOP 1mth,qty,
SIGN(S1.qty -
(SELECT TOP (1) qty
FROM dbo.Sales AS S2
WHERE S2.mth < S1.mth
ORDER BY S2.mth DESC)) AS sgn
FROM dbo.Sales AS S1
May 21, 2013 at 2:20 am
SELECTmth, qty,
SIGN( S1.qty -
(
SELECT TOP (1) qty
FROM dbo.Sales AS S2
WHERE S2.mth < S1.mth
ORDER BY S2.mth DESC
/*
This query is part of your SELECT statement
It will always return a single value
That value is then subtracted from S1.qty
This doesnot affect your final result
*/
)
) AS sgn
FROM dbo.Sales AS S1
/*
There is no WHERE clause to filter the number of records
Hence, it will return all the rows from table dbo.Sales
*/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 2:26 am
eklavu (5/21/2013)
Are you pulling the top first record? No t sure if I understand clearly.TOP (1) should be in your main table not on the derived table "sgn"
SELECT TOP 1mth,qty,
SIGN(S1.qty -
(SELECT TOP (1) qty
FROM dbo.Sales AS S2
WHERE S2.mth < S1.mth
ORDER BY S2.mth DESC)) AS sgn
FROM dbo.Sales AS S1
I think the objective here is to select all the sales orders and an additional column "sgn" which denotes if the previous order was bigger or smaller than the current one.
A value of 1 means the current order is bigger, a value of -1 means the current order is smaller, a value of 0 means the current order is same as the previous one.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 7:25 am
That is correct, -1 0 or 1 depending on the previous value compared to the current one. It is working correctly, I just am not processing why I'm getting more then one row. The post with the script and comment was helpful though.
May 21, 2013 at 8:30 am
lmacdonald (5/21/2013)
That is correct, -1 0 or 1 depending on the previous value compared to the current one. It is working correctly, I just am not processing why I'm getting more then one row. The post with the script and comment was helpful though.
Your query is similar to the below one
SELECTmth, qty, SIGN( S1.qty - 10 ) AS sgn
FROM dbo.Sales AS S1
In your query, instead of the hardcoded value "10", you have a subquery which returns the value from the previous order
You can check the below link on subqueries for more information
http://msdn.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 22, 2013 at 4:20 am
The TOP keyword in your statement is only returning one value, however it is the outer query that is returning more than one row. If you want one row you need to add TOP (1) to your outer query as well.
Joie Andrew
"Since 1982"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply