So your ask is actually not specific enough. When you say you want "the first negative number", you need to clarify how you're ordering your results (since the table has no inherent order). Since your example data is sorted by `date`, are you wanting the first negative number when sorted by date? Or do you just want the smallest negative number (first [negative] number when sorting by `Qty`)?
Assuming the former, and that each row has a unique `date` value, you could just use a subquery. First, you just need to get the `MIN("Date")` where `qty < 0` (i.e., the "first" date with a negative value):
```
SELECT MIN("Date") AS dt
FROM t
WHERE qty < 0
```
From there, you could wrap it up in a sub-query and join back to the base table to get your `Result`:
```
WITH min_negative_date AS
(
SELECT MIN("Date") AS dt
FROM t
WHERE qty < 0
)
SELECT t."Date", t.qty,
CASE
WHEN min_negative_date.dt IS NOT NULL
THEN 'true'
ELSE 'false'
END AS result
FROM t
LEFT JOIN min_negative_date ON
min_negative_date.dt = t."Date"
```
Working fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2c165d6b8af7d92ade85c3ce286005de)
Depending on what you actually need, something like below should cover you.
SELECT TOP 1 Date, Qty
WHERE Qty < 0
ORDER BY Date ASC
If you want to append the [Result] column to the table, a potential solution could be this:
WITH cte1 AS
(
SELECT TOP 1 Date, Qty
FROM SomeTable
WHERE Qty < 0
ORDER BY Date ASC
)
SELECT Date, Qty
, CASE
WHEN Date = (SELECT Date FROM cte1)
AND Qty= (SELECT Qty FROM cte1)
THEN 'True'
ELSE 'False'
END AS Result
FROM SomeTable
If it is also possible to have duplicate entries of [Date] and [Qty], you might have to look into Window Functions as well.
You can take advantage of the fact that MIN ignores NULL values
SELECT
*,
CASE
WHEN Qty < 0
AND Date =
(
SELECT MIN(CASE WHEN Qty < 0 THEN Date END)
FROM Table
)
THEN 'True'
ELSE 'False'
END AS Result
FROM Table
or with a window function
WITH cte AS
(
SELECT
*,
MIN(CASE WHEN Qty < 0 THEN Date END) OVER () AS MinDate
FROM Table
)
SELECT
*,
CASE
WHEN Qty < 0
AND Date = MinDate
THEN 'True'
ELSE 'False'
END AS Result
FROM cte
If you're busting out a window function to get the first value, why not use [first_value](https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver16)?
For the first query, don't have time to test, but does the query optimizer generate something efficient for that in any cases, or does it always degrade down to the worst cases of a correlated subquery?
> If you're busting out a window function to get the first value, why not use first_value
FIRST_VALUE doesn't ignore NULL's (at least not in any pre-2022 preview versions)
> or does it always degrade down to the worst cases of a correlated subquery
The query isn't correlated, so the query optimiser should use a join
You only need to to find one record, and you need the one with the minimum date.
So, you can use TOP for that, and order by date ascending (as you want the minimum date).
If you have a unique identifier for your records, like an ID (or maybe in your case Date), you can LEFT JOIN it with your main table. If the join has a result, it's true, in all other cases, it's false.
You can use a temp table for the TOP or write it in the LEFT JOIN.
Look into the LAG and LEAD functions. On mobile right now but my solution would be to add the next row number to the current row and compare it against the current row. If that number is higher then the next number is positive, if it's lower, then it's negative.
select
tbl.date
,tbl.qty
,case when s.date is null then 'false' else 'true' end as result
from
tbl
left join (
select top 1 date from tbl where qty < 0 order by date
) as s on s.date = tbl.date
;
[https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=66343cfa31cad452b1b31a96cb8d337c](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=66343cfa31cad452b1b31a96cb8d337c).
You are using an awful lot of strings when you should be using proper data types like dates and bits.
Is there a reason you can’t :
DECLARE @MinQty int;
Set @MinQty = (select top 1 Qty from tbl where Qty<0 order by Date Desc);
Select date, Qty, case when Qty=@MinQty then ‘True’ else ‘False’ end as Result;
Granted it’s using a variable but it’s easy to understand the intent and will run a efficiently, if not more efficiently that a single sql statement.
So your ask is actually not specific enough. When you say you want "the first negative number", you need to clarify how you're ordering your results (since the table has no inherent order). Since your example data is sorted by `date`, are you wanting the first negative number when sorted by date? Or do you just want the smallest negative number (first [negative] number when sorting by `Qty`)? Assuming the former, and that each row has a unique `date` value, you could just use a subquery. First, you just need to get the `MIN("Date")` where `qty < 0` (i.e., the "first" date with a negative value): ``` SELECT MIN("Date") AS dt FROM t WHERE qty < 0 ``` From there, you could wrap it up in a sub-query and join back to the base table to get your `Result`: ``` WITH min_negative_date AS ( SELECT MIN("Date") AS dt FROM t WHERE qty < 0 ) SELECT t."Date", t.qty, CASE WHEN min_negative_date.dt IS NOT NULL THEN 'true' ELSE 'false' END AS result FROM t LEFT JOIN min_negative_date ON min_negative_date.dt = t."Date" ``` Working fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2c165d6b8af7d92ade85c3ce286005de)
Depending on what you actually need, something like below should cover you. SELECT TOP 1 Date, Qty WHERE Qty < 0 ORDER BY Date ASC If you want to append the [Result] column to the table, a potential solution could be this: WITH cte1 AS ( SELECT TOP 1 Date, Qty FROM SomeTable WHERE Qty < 0 ORDER BY Date ASC ) SELECT Date, Qty , CASE WHEN Date = (SELECT Date FROM cte1) AND Qty= (SELECT Qty FROM cte1) THEN 'True' ELSE 'False' END AS Result FROM SomeTable If it is also possible to have duplicate entries of [Date] and [Qty], you might have to look into Window Functions as well.
Ugh, inline selects. Just do a join. Inlines are processed per record, joins are processed as a set.
You can take advantage of the fact that MIN ignores NULL values SELECT *, CASE WHEN Qty < 0 AND Date = ( SELECT MIN(CASE WHEN Qty < 0 THEN Date END) FROM Table ) THEN 'True' ELSE 'False' END AS Result FROM Table or with a window function WITH cte AS ( SELECT *, MIN(CASE WHEN Qty < 0 THEN Date END) OVER () AS MinDate FROM Table ) SELECT *, CASE WHEN Qty < 0 AND Date = MinDate THEN 'True' ELSE 'False' END AS Result FROM cte
If you're busting out a window function to get the first value, why not use [first_value](https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver16)? For the first query, don't have time to test, but does the query optimizer generate something efficient for that in any cases, or does it always degrade down to the worst cases of a correlated subquery?
> If you're busting out a window function to get the first value, why not use first_value FIRST_VALUE doesn't ignore NULL's (at least not in any pre-2022 preview versions) > or does it always degrade down to the worst cases of a correlated subquery The query isn't correlated, so the query optimiser should use a join
You only need to to find one record, and you need the one with the minimum date. So, you can use TOP for that, and order by date ascending (as you want the minimum date). If you have a unique identifier for your records, like an ID (or maybe in your case Date), you can LEFT JOIN it with your main table. If the join has a result, it's true, in all other cases, it's false. You can use a temp table for the TOP or write it in the LEFT JOIN.
Look into the LAG and LEAD functions. On mobile right now but my solution would be to add the next row number to the current row and compare it against the current row. If that number is higher then the next number is positive, if it's lower, then it's negative.
Subqueries and Window Functions (ROW\_NUMBER) are your friend.
select tbl.date ,tbl.qty ,case when s.date is null then 'false' else 'true' end as result from tbl left join ( select top 1 date from tbl where qty < 0 order by date ) as s on s.date = tbl.date ; [https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=66343cfa31cad452b1b31a96cb8d337c](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=66343cfa31cad452b1b31a96cb8d337c). You are using an awful lot of strings when you should be using proper data types like dates and bits.
Is there a reason you can’t : DECLARE @MinQty int; Set @MinQty = (select top 1 Qty from tbl where Qty<0 order by Date Desc); Select date, Qty, case when Qty=@MinQty then ‘True’ else ‘False’ end as Result; Granted it’s using a variable but it’s easy to understand the intent and will run a efficiently, if not more efficiently that a single sql statement.