SQLTeam.com | Weblogs | Forums

Select earliest rows where column is null

Hi, I have an SQL pricing table where one of the columns is date, another is the price information.

How do I select the rows with earliest date where the price is null?

There are multiple funds in the table, each fund can have different date which the earliest date is null. How do I display all these rows with the earliest date?

E.g. Fund 1, 23 January
Fund 2, 10 March
Fund 3, 9 April

These dates are the earliest dates for each fund that price is null.

I am currently using this code

select dbo.Benchmark_Details.BenchmarkID, Date from dbo.Benchmark_Details where date in
(select top 1 date from dbo.Benchmark_Details where Benchmark_Price is null order by date)

but this takes the earliest of all the dates and applies it to all the funds instead of each fund having its earliest date individually. Also if the dates are not the same then sometimes it returns blank result

Select min(date) from
(
Select from table where column is null
) TableNullData

  1. Select from table where column is null
    This gets only null records

Select min date
This gets the min date from 1)

How about selecting latest date where price is not null?

This is my existing code

select BenchmarkID, Date from dbo.Benchmark_Details where date in
(select top 1 date from dbo.Benchmark_Details where Benchmark_Price is not null order by date desc)

My main issue is that there are many individual funds in this table, and each fund might contain a different latest date where the price is not null. When I specify where date in (select top 1 date... it gets the maximum date of all the funds... The ideal query should not do this.

Partition by fund
Clause

Partition by does the logic
For each fund separately ..

@harishgg1 do you have a sample query for this? Thanks

select row_number() over (partition by customerId order by orderId)
as OrderNumberForThisCustomer
from Orders

I think you will better
Understand

With sample data
And query
To show output also

I am not at my desktop

Please Google search
Lots of articles
With samples and
Illustrations