Wednesday 15 April 2015

Interview Question - How to separate positive and negative numbers from column

This one is the interview question asked to one of my friend for the post of t-SQL developer. 

We have a table with ID column containing following values in it 



And the Output should be 



How would you go about it and write the query for it.


Before proceeding for a solution lets build the table and populate it with the required values.

DECLARE @TAB TABLE
(
ID SMALLINT
)

INSERT INTO @TAB 
SELECT 71 UNION ALL 
SELECT 24 UNION ALL
SELECT -12 UNION ALL
SELECT -129 UNION ALL
SELECT 13 UNION ALL
SELECT 0 UNION ALL
SELECT -90

SELECT * FROM @TAB


There might be multiple solutions for this problem but this is what I could think of 

;WITH Positive AS
(
SELECT 
ID AS Pos
,ROW_NUMBER() OVER (ORDER BY ID) AS RANK1
FROM @TAB
WHERE ID >= 0
),Negative AS
(
SELECT 
ID AS Neg
,ROW_NUMBER() OVER (ORDER BY ID DESC) AS RANK2
FROM @TAB
WHERE ID < 0
)
SELECT Pos,Neg 
FROM Positive
FULL OUTER JOIN Negative
ON RANK1 = RANK2

2 comments:

  1. Answer
    select
    positive=case when id>0 then id end,
    negative=case when id<0 then id end
    from
    table_name;

    ReplyDelete
    Replies
    1. That won't give you the expected answer

      Delete

bloggerwidgets