SQL query to group by if column value exists, else list all records

I have some reports in a SQL database. Some reports have a report number, some reports don't have a report number.

Report_id	Report_created_by	Versions	Report_number
1	John Smith	1	101
2	John Smith	2	101
3	Jack Johson
4	Madeline Joe

I would like to group reports that have a report number, and list all reports that don't have a report number. So I should get a result like this:

Report_id	Report_created_by	Versions	Report_number
1	John Smith	1	101
3	Jack Johson
4	Madeline Joe

How should I write my SQL query?

Note: I am already using a UNION to solve this but am looking for a better solution.

Thank you.

Solution

You can get the row number after sorting the versions for every report_created_by using row_number() over (), then select the first record:

with lowest_report_versions as (
  select 
    reports.*, 
    row_number() over (partition by report_created_by order by versions) as r
   from reports
)
select * 
from lowest_report_versions 
where r = 1 
order by report_id

db<>fiddle