SQL how to make null values come last when sorting ascending
select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate
SQL Find the first non null value with arbitrary order by
Just do :
SELECT TOP 1 Value
FROM mytable
WHERE Value IS NOT NULL
ORDER BY ID
To get the last not null value switch the ordering:
ORDER BY ID DESC
To expand the same logic to a greater number of columns you can use the following query:
SELECT (SELECT TOP 1 Value1 FROM mytable
WHERE Value1 IS NOT NULL ORDER BY ID) AS min_Value1,
(SELECT TOP 1 Value2 FROM mytable
WHERE Value2 IS NOT NULL ORDER BY ID) AS min_Value2
Demo here
How to order by column with non-null values first in sql
See Sort Values Ascending But NULLS Last
basically
SELECT *
FROM @Temp
ORDER BY CASE WHEN LastName IS NULL THEN 1 ELSE 0 END, LastName
How to get the first non-NULL value in SQL?
You can put that CASE in the ORDER BY of the FIRST_VALUE.
Then the null's will be sorted last for that function.
create table test
(
pid int,
pdate date,
BMI decimal(4,1)
)
insert into test (pid, pdate, BMI) values
(1, '2000-01-01', NULL)
, (1, '2003-05-01', 18.5)
, (1, '2002-07-15', 24.9)
, (2, '2009-09-25', NULL)
, (2, '2015-04-18', 21.7)
;
select *
, first_value(BMI) over (partition by pid order by case when BMI is not null then 1 else 2 end, date(pdate)) as firstBMI
from test
order by pid, pdate
pid | pdate | BMI | firstBMI
:-- | :--------- | :--- | :-------
1 | 2000-01-01 | null | 24.9
1 | 2002-07-15 | 24.9 | 24.9
1 | 2003-05-01 | 18.5 | 24.9
2 | 2009-09-25 | null | 21.7
2 | 2015-04-18 | 21.7 | 21.7
db<>fiddle here
Finding the first non-null in a partition
We could try the following approach. Here we are applying ROW_NUMBER with a partition on person and also a partition on whether or not the marital status value is NULL. We then use the most recent non NULL marital status value, per person, to fill in any NULL missing marital status values.
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY Person,
CASE WHEN MaritalStatus IS NULL THEN 0 ELSE 1 END
ORDER BY Year DESC) rn
FROM mytable
)
SELECT t1.Person, t1.Year, COALESCE(t1.MaritalStatus, t2.MaritalStatus) AS MaritalStatus
FROM mytable t1
LEFT JOIN cte t2
ON t2.Person = t1.Person AND
t2.MaritalStatus IS NOT NULL AND
t2.rn = 1;
Demo
Fastest way to get the first non null value of a column with in a range of dates in SQL SERVER
I assume you want to order the dates. Otherwise, you will not be able to find the first not null foo value. Because, the term first is relative. You need to have a order by clause to find the first not null value.
I have given sample code for your reference.
CREATE TABLE #test (datevalue DATE, foo INT);
CREATE CLUSTERED INDEX cix_test ON #test (datevalue);
INSERT INTO #test
VALUES ('20181001', NULL), ('20181101', 10), ('20181201', NULL), ('20190101', 1);
SELECT TOP 1 FIRST_VALUE(foo) OVER (
ORDER BY datevalue
)
FROM #test
WHERE datevalue BETWEEN '20181001'
AND '20181101'
AND foo IS NOT NULL;
Ordering within group and selecting non-null values
You may use match_recognize to capture rows with non-null values and reference them in a measures clause:
select x, y, z
from test_tab
match_recognize (
partition by x
order by z asc nulls last
measures
/*The first value of Z (nulls last)*/
first(any_.z) as z,
/*The first non-null value of Y:
captured either by any_ pattern
or by other that skips all nulls first
*/
coalesce(first(any_.y), first(other.y)) as y
/*Any row
followed by zero or more Y with null
followed by any number of any non-null Y rows for the same X
*/
pattern (any_ y_null* other*)
define
y_null as y is null
)
Or from Oracle 21c you may reuse window specification by declaring it at the bottom of select statement and use your original approach:
select distinct
x,
first_value(y ignore nulls) over fv as y,
first_value(z ignore nulls) over fv as z
from test_tab
window fv as (
partition by x
order by z asc nulls last
rows between unbounded preceding and unbounded following
)
Given this sample data:
create table test_tab(X, Y, Z) as
select 'a', 'a0', NULL from dual union all
select 'a', NULL, 1 from dual union all
select 'a', 'a2', 2 from dual union all
select 'b', 'b1', 1 from dual union all
select 'b', 'b2', 2 from dual
both the queries return this result:
X Y Z a a2 1 b b1 1
Related Topics
How to Set The Starting Point for The Primary Key (Id) Column in Postgres via a Rails Migration
How to Best Handle the Storage of Historical Data
Alter Table to Modify Default Value of Column
How This SQL Injection Works? Explanation Needed
How to Multiply All Values Within a Column with SQL Like Sum()
Query SQL Server Database from Native iOS Application
Rake Task to Truncate All Tables in Rails 3
Extract Time Part from Timestamp Column in Oracle
Select Statement in Sqlite Recognizing Row Number
SQL Query for Courses Enrolment on Moodle
What Is The SQL Used to Do a Search Similar to "Related Questions" on Stackoverflow
How to Use MySQL Index Columns
Simple, Fast SQL Queries for Flat Files
What Is The Advantage of Using Varbinary Over Varchar Here
Sql Collation Conflict When Comparing to a Column in a Temp Table
Find The Time Difference Between Two Consecutive Rows in The Same Table in Sql