SIGN

  • 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....

  • 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

  • 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

  • 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

    */


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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.

  • 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


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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