How can I rewrite this SELECT?

Question

In the sample code below the last select has to scan what in the real database is a multimillion row table. Seems like I should be able to rewrite this so it only has to scan one time but I am failing to see the solution.

I want all the A, C and F types but only the first row for a JobNumber when the type is N.

Thanks for any help.

DECLARE @JobTransaction TABLE
(	 JobTransactionID	INT PRIMARY KEY IDENTITY
	,JobNumber	INT	NOT NULL
	,Type		CHAR(1)
);

INSERT @JobTransaction  
(	 JobNumber				
	,Type					
)	VALUES
 (1, 'A')
,(1, 'C')
,(1, 'F')
,(1, 'N')
,(1, 'A')
,(1, 'C')
,(1, 'F')
,(1, 'N');

SELECT *
FROM @JobTransaction;
--	
SELECT  JobTransactionID	
	   ,JobNumber			
	   ,Type
FROM @JobTransaction
WHERE 	JobTransactionID IN 
	(SELECT MIN(JobTransactionID)
	  FROM @JobTransaction
	   WHERE Type = 'N'
	   GROUP BY JobNumber)
   OR Type in ('C', 'F', 'A');			

---

Tom G.

Answer 1

Will this variation be better:

DECLARE @JobTransaction TABLE
(	 JobTransactionID	INT PRIMARY KEY IDENTITY
	,JobNumber	INT	NOT NULL
	,Type		CHAR(1)
);

INSERT @JobTransaction  
(	 JobNumber				
	,Type					
)	VALUES
 (1, 'A')
,(1, 'C')
,(1, 'F')
,(1, 'N')
,(1, 'A')
,(1, 'C')
,(1, 'F')
,(1, 'N');

;with cte as (select *, 
row_number() over (partition by JobNumber, [type] order by JobTransactionID) as Rn 
from @JobTransaction)

select * from cte where [Type] in ('A','C','F') OR (type = 'N' and Rn =1)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Try this:

DECLARE @JobTransaction TABLE
(	 JobTransactionID	INT PRIMARY KEY IDENTITY
	,JobNumber	INT	NOT NULL
	,Type		CHAR(1)
);

INSERT @JobTransaction  
(	 JobNumber				
	,Type					
)	VALUES
 (1, 'A')
,(1, 'C')
,(1, 'F')
,(1, 'N')
,(1, 'A')
,(1, 'C')
,(1, 'F')
,(1, 'N');

SELECT *
FROM @JobTransaction;
--	

SELECT TOP 1 * FROM @JOBTRANSACTION WHERE TYPE='F' 
UNION ALL
SELECT * FROM @JOBTRANSACTION WHERE TYPE<>'F' 

Answer 3

This still looks like two passes over the table.

---

Tom G.

Answer 4

This look good I will give it a try and post as an answer after.

---

Tom G.