CODEWITHSUNDEEP
sql-server
Rapunzo
Rapunzo

Reputation: 986

List all negative numbers and minimum positive number from table

This an example table and I want to get all negative numbers and minimum positive number (0 included) for per id

    declare  @tbl table(id INT, value decimal, someData varchar(10))

    insert into @tbl
    values(1,-3,123),(1,-2,234),(1,-1,345),(1,3,456),(1,4,567),(2,-4,678),(2,-2,789),(2,1,890),(2,2,135),(3,-5,246),(3,10,357)

    select * from @tbl where value < 0 union 
    select id, min(value),someData from @tbl WHERE value > 0 group by id,somedata

I am trying to find a solution by separating minuses and pluses. but because of someData I cannot group by them as I need.

desired result is:

1   -3  123
1   -2  234
1   -1  345
1   3   456
2   -4  678
2   -2  789
2   1   890
3   -5  246
3   10  357

Also its a long working query so I dont want to make double select. Is it possible to make it in one select?

Upvotes: 0

Views: 878

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93724

using Window function u can get minimum values. and use UNION to combine the results. TRY THIS..

;WITH CTE
     AS (SELECT Row_number() OVER (PARTITION BY ID ORDER BY value) RN, *
         FROM   @tbl
         WHERE  value > 0) 
SELECT *
FROM   @tbl
WHERE  value < 0
UNION ALL
SELECT ID,value,someData
FROM   CTE
WHERE  RN = 1

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

This should work:

;With separated as (
    select *,ROW_NUMBER() OVER (PARTITION BY SIGN(value),id ORDER BY value) as rn
    from @tbl
)
select * from separated where SIGN(value) < 0 or rn=1

You haven't said what should happen if value is 0 so the above may or may not be correct if your data contains some.

SIGN is a little-used but occasionally useful function in SQL Server, returning -1, 0 or +1 and allowing us to easily partition the groups of numbers into 3 groups.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425043

Attack it as a single select with an OR where clause, using a subquery to get the minimum positive number:

select * from @tbl
where value < 0
or value = (
    select min(value)
    from @tbl
    where value > 0)

Upvotes: 0

Richard
Richard

Reputation: 109015

Using a union to combine the conditions is likely easiest. But to select the row with the minimum positive value requires an sub-query, and the assumption that ID is a unique identifier:

select id,value,somdata from @tbl where value < 0
union
select id,value,somdata from @tbl
where value = (select min(value) from @tbl where value > 0)

(That won't include rows with a zero value, but the question doesn't say which side they should fall.)

Upvotes: 0

Related Questions