Return the record from in line query in one situation or main table in another

Transact-SQL
1

I have a query which returns a record from a contract items table. At the moment it returns (correctly) the record which has the highest number in the column RECORDER:

SELECT f.ITEMNO
	,f.CONTNO
	,f.STATUS
	,f.HIREDATE
	,f.DOCDATE#5
	,f.OHREC
	,f.RECORDER
FROM (
	SELECT ITEMNO
		,MAX(RECORDER) RECORDER
	FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[CONTITEMS]
	GROUP BY ITEMNO
	) AS x
INNER JOIN [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[CONTITEMS] f ON f.ITEMNO = x.ITEMNO
	AND f.RECORDER = x.RECORDER
WHERE f.ITEMNO = '01BK0039'

However, I want to change this logic so that it is as follows:

If the record status is 1, use that line and return the related information from that record.

If the record status <> 1 then use the RECORDER column and return the related information from the record with the highest number in that RECORDER column.

Only one record should ever be returned for a particular ITEMNO

In the example below, I want it to return the record with the RECORDER of 581043 as that item has a record status of 1:

+----------+------------+--------+-------------------------+-------------------------+-------+----------+
|  ITEMNO  |   CONTNO   | STATUS |        HIREDATE         |        DOCDATE#5        | OHREC | RECORDER |
+----------+------------+--------+-------------------------+-------------------------+-------+----------+
| 01BK0039 | 00WA002946 |      1 | 2015-07-24 00:00:00.000 | 1899-12-30 00:00:00.000 |       |   581043 |
| 01BK0039 | 00WA002319 |      2 | 2015-03-02 00:00:00.000 | 2015-06-30 00:00:00.000 |       |   693603 |
| 01BK0039 | 00WA002712 |      2 | 2015-03-15 00:00:00.000 | 2015-03-30 00:00:00.000 |       |   693688 |
+----------+------------+--------+-------------------------+-------------------------+-------+----------+

What's the best way to achieve this, seems like I need a CASE somewhere, but I can't figure out how to just return the 1 record each time.

Any help with this would be appreciated.

2 
WITH SelectOrder
AS
(
	SELECT f.ITEMNO, f.CONTNO, f.[STATUS], f.HIREDATE
		,f.[DOCDATE#5], f.OHREC, f.RECORDER
		,ROW_NUMBER() OVER (PARTITION BY ITEMNO ORDER BY CASE WHEN f.[STATUS] = 1 THEN 1 ELSE 2 END, f.RECORDER DESC) AS rn
	FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[CONTITEMS] f
	WHERE f.ITEMNO = '01BK0039'
)
SELECT ITEMNO, CONTNO, [STATUS], HIREDATE, [DOCDATE#5], OHREC, RECORDER
FROM SelectOrder
WHERE rn = 1;
1 Like
3

That seems to work very well - thanks for your help!

Martyn

4

As you seem to be using a remote server it might be more efficient to do something like:

DECLARE @ItemNo varchar(20) = '01BK0039';
EXEC [LON1-ONE-APP03\INSPHIRE].[master].dbo.sp_executesql
N'WITH SelectOrder
AS
(
	SELECT ITEMNO, CONTNO, [STATUS], HIREDATE
		,[DOCDATE#5], OHREC, RECORDER
		,ROW_NUMBER() OVER (PARTITION BY ITEMNO ORDER BY CASE WHEN [STATUS] = 1 THEN 1 ELSE 2 END, RECORDER DESC) AS rn
	FROM [IHDATA2].[DBO].[CONTITEMS]
	WHERE ITEMNO = @ItemNo
)
SELECT ITEMNO, CONTNO, [STATUS], HIREDATE, [DOCDATE#5], OHREC, RECORDER
FROM SelectOrder
WHERE rn = 1'
	,N'@ItemNo varchar(20)'
	,@ItemNo;
5

Hi Ifor,

I'm not familiar with this.
Can you explain the difference with your previous solution?

6

With four part naming queries if collation etc does not line up the engine can decide to bring all the rows from the remote instance before applying filters.

Using OPENQUERY or, in this case, the remote copy of sp_executesql gets around the problem.

If the execution of the original query is fast I would not worry about it.

1 Like
7

Hi Ifor,

Sorry for my late response.

This trick may come in handy at some point. Thank you for sharing!

Wim