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
- 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