CODEWITHSUNDEEP
sqlsql-servert-sql
Gleb
Gleb

Reputation: 1432

Compare values in Sql query

I have some tables and sql query with join to select from this tables. Query result looks like:

number|name |value
------------------
1     |name1| 2.48
2     |name1| 1.32
3     |name1| 1.32

I need to rewrite this query to add one more field depends of comparison of value field. For example: if it is the first row I need 0, if previous value bigger then I need 1, else 2.

Result I want to have looks like this:

number|name |value|status
--------------------------
1     |name1| 2.48|0
2     |name1| 1.32|2
3     |name1| 1.57|1

Also I know about case when, but I don't know how I can compare values. The best way, as I see, to iterate through all rows in the result. May be there is some other ways?

Upvotes: 1

Views: 3889

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

In SQL Server 2012+, you can use lag():

select t.*,
       (case when lag(value) over (order by number) is null then 0
             when value > lag(value) over (order by number) then 1
             else 2
        end) as status
from table t;

In earlier versions, you can use a correlated subquery or outer apply to do the same thing.

Upvotes: 2

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

Reputation: 93754

If you are not using MSSQL 2012+ then use this.

You need to left join with the same table to do this.

;WITH cte
     AS (SELECT Row_number()OVER(partition BY name ORDER BY number) Rn,
                *
         FROM   Yourtable)
SELECT a.*,
       CASE
         WHEN b.rn IS NULL THEN 0
         WHEN a.value < b.value THEN 2
         ELSE 1
       END [Status]
FROM   cte a
       LEFT JOIN cte b
              ON a.Rn = b.Rn + 1

or use Outer Apply

SELECT a.number,a.name,a.value, CASE
             WHEN b.number IS NULL THEN 0
             WHEN a.value < b.value THEN 2
             ELSE 1
           END [Status]
FROM   Yourtable a
       OUTER apply (SELECT TOP 1 number,
                                 value
                    FROM   Yourtable  b
                    WHERE  a.name = b.name
                           AND a.number > b.number
                    ORDER  BY number DESC) b

Upvotes: 2

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use Self Join.

SELECT A.number, A.name, A.Value, 

               CASE WHEN A.Value ISNULL THEN 0
                    WHEN ISNULL(A.value, 0) > ISNULL(B.Value, 0) THEN 1 
                    ELSE 2 
                END AS Status 
    FROM Table AS A INNER JOIN Table AS B ON A.number - 1 = B.number

Upvotes: 1

Related Questions