CODEWITHSUNDEEP
sqlsql-server
Admin Softisans
Admin Softisans

Reputation: 59

Get minimum value from records

I have following table locationsRoute.

locationId          RouteOrder       state  
25433                  2.0000           1
25434                  3.0000           NULL
25747                  4042.0000       1
25760                  4063.0000       1
25764                  4071.0000       1
48349                  4081.0000       NULL
48350                  4081.0000       NULL
48366                  4082.0000       NULL
48370                  4082.0000       NULL
25776                  4095.0000       NULL

I have to get following result. In minimumRouteOrder column , I have to get minimum routeOrder of location which state is null.

locationId  RouteOrder       minimumRouteOrder
25433       2.0000            3.0000
25434       3.0000            3.0000
25747       4042.0000         3.0000
25760       4063.0000         3.0000
25764       4071.0000         3.0000
48349       4081.0000         3.0000
48350       4081.0000         3.0000
48366       4082.0000         3.0000
48370       4082.0000         3.0000
25776       4095.0000         3.0000

Can you please help me to create sql query to get above result?

Upvotes: 0

Views: 78

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use a window function:

select lr.*,
       min(case when state is null then RouteOrder end) over () as minimumRouteOrder
from locationsRoute lr;

Upvotes: 1

PSK
PSK

Reputation: 17943

You can also try.

SELECT *, 
       (SELECT Min(routeorder) from [locationsRoute]
        WHERE  state IS NULL) minimumRouteOrder 
FROM   [locationsRoute]

Upvotes: 0

MahPa
MahPa

Reputation: 1

Use MIN()

Select locationId, RouteOrder,
(SELECT MIN(RouteOrder) FROM locationsRoute WHERE state IS NULL) AS minimumRouteOrder
FROM locationsRoute

Upvotes: 0

Related Questions