CODEWITHSUNDEEP
sqlsql-server
Gabe
Gabe

Reputation: 6065

Query to return 2nd value if there are two, null if just one

The filtered table (code) may have these values

 code_type    date              id          
 TM       1/1/2011        2342 (random unique id)
 TM       2/1/2011        3298  (random unique id)

The table may have no TM records, one TM record, or it may have two TM records (never more).

If there's only one value, my output needs to be like this (blank or null values). If there's two records, the code2 and date2 fields would be filled in. If there are no TM records, all fields need to be null or empty.

code_type1   date1          code_type2     date2
TM       1/1/2011

I'm doing this in a subquery in the select clause, not sure if that will make any difference. I'm thinking I need a CASE statement?

Using SQL Server 2008

edit: The code id is random and not necessarily 1 or 2 as I had originally written this

Upvotes: 1

Views: 822

Answers (3)

Cowan
Cowan

Reputation: 37543

Given your restriction that there will NEVER be more than 2 records, this is very easy to do in a cross-platform way with only standard SQL; all you need is a LEFT JOIN.

SELECT
  Code1.Code_Type AS Code_Type1,
  Code1.Date AS Date1,
  Code2.Code_Type AS Code_Type2,
  Code2.Date AS Date2
FROM Code AS Code1
LEFT JOIN Code AS Code2
  ON (Code1.Code_Type = Code2.Code_Type)
  AND (Code1.ID < Code2.ID)
LEFT JOIN Code AS EarlierCode
  ON (Code1.Code_Type = EarlierCode.Code_Type)
  AND (Code1.ID > EarlierCode.ID)
WHERE
  (EarlierCode.ID IS NULL)

This is assuming that 'lower ID' is the way to determine which one is 'code1' and which is 'code2'. If it's date, then use that instead in the last line of each JOIN.

An explanation, if it's not clear: you start with each row in the table (Code1). You then join the table onto itself twice: Code2 represents anything with a higher ID (there'll be only one of these, according to your premis), and EarlierCode represents a lower one. Because if you DO have two rows, you don't want it twice (once with the earlier value in Code1 and the later in Code2, once with the later in Code1 and Code2 left null), you have 'EarlierCode', which represents something earlier than Code1. If there is one, you leave that row out (the WHERE clause).

Upvotes: 1

lamarant
lamarant

Reputation: 3390

SELECT 
  code1 = (SELECT code FROM TABLENAME WHERE id = 1),
  date1 = (SELECT [date] FROM TABLENAME WHERE id = 1),
  code2 = (SELECT code FROM TABLENAME WHERE id = 2),
  date2 = (SELECT [date] FROM TABLENAME WHERE id = 2)

You can test this with the following script:

DECLARE @code TABLE (
code char(2),
[date] char(30),
id int )

INSERT INTO @code (code, [date], id) VALUES ('TM', '1/1/2011', 1)
INSERT INTO @code (code, [date], id) VALUES ('TM', '2/1/2011', 2)

SELECT 
  code1 = (SELECT code FROM @code WHERE id = 1),
  date1 = (SELECT [date] FROM @code WHERE id = 1),
  code2 = (SELECT code FROM @code WHERE id = 2),
  date2 = (SELECT [date] FROM @code WHERE id = 2)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

WITH YourQuery(code,[date],id) As
(
SELECT  'TM', '1/1/2011', 1 union all
SELECT  'TM', '2/1/2011', 2 
),
Numbered AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY id) RN
FROM YourQuery
)
SELECT 
     code1 = MAX(CASE WHEN RN=1 THEN code END),
     date1 = MAX(CASE WHEN RN=1 THEN [date] END),
     code2 = MAX(CASE WHEN RN=2 THEN code END),
     date2 = MAX(CASE WHEN RN=2 THEN [date] END)
FROM Numbered

Upvotes: 1

Related Questions