I have some records that they have a Priority field and I want to select some fields of these records with the highest rank in one record. If the value of the field is null it'd choose the lower priority one.
You can see sample data in the image below: the orange table is the source and the blue one is the result.
CREATE TABLE MyView(Id INT ,[Priority] INT ,[F1] INT ,[F2] INT ,[F3] INT);
INSERT INTO [dbo].[MyView]
VALUES ( 15, 4, NULL, 3, 2 ),
( 20, 3, 2, 1, NULL ),
( 28, 2, NULL, 0, 9 ),
( 36, 1, 5, 1, NULL );
DROP TABLE [dbo].[MyView];
Well, I can't think of anything too efficient for this, but try this:
SELECT TOP 1 t.f1, (SELECT TOP 1 s.f2 FROM YourTable s ORDER BY CASE WHEN s.f2 IS NULL THEN 1 ELSE 0 END, s.priority DESC) as f2, (SELECT TOP 1 q.f3 FROM YourTable q ORDER BY CASE WHEN q.f3 IS NULL THEN 1 ELSE 0 END, q.priority DESC) as f3 FROM YourTable t ORDER BY CASE WHEN t.f1 IS NULL THEN 1 ELSE 0 END, t.priority DESC