query

Question

Post reply

query

satishthota

SSC Eights!

Points: 959
More actions
April 8, 2009 at 9:09 pm

#208238

I had T1 Table
PersonId OID
P1 O1
P2 O7
P3 O1
Query to retrieve following output
PersonId OID
P1 O7
P2 O1
P3 O7

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

arun.sas

SSChampion

Points: 11831

April 8, 2009 at 9:20 pm

#974813

Hi,

At what condition?

ARUN SAS

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

satishthota (4/8/2009)
I had T1 Table
PersonId OID
P1 O1
P2 O7
P3 O1
Query to retrieve following output
PersonId OID
P1 O7
P2 O1
P3 O7

I see what you're doing, reversing the values, but what would be the point of doing something like this? What business or functional need are you fulfilling with such a query?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Sure:

DROP TABLE #T1

CREATE TABLE #T1 (PersonId VARCHAR(2), OID VARCHAR(2))

INSERT INTO #T1 (PersonId, OID)

SELECT 'P1', 'O1' UNION ALL

SELECT 'P2', 'O7' UNION ALL

SELECT 'P3', 'O1'

SELECT a.PersonId, (SELECT OID FROM #T1 WHERE PersonId = COALESCE((SELECT MIN(PersonId) FROM #T1 WHERE PersonId > a.PersonId),

(SELECT MAX(PersonId) FROM #T1 WHERE PersonId < a.PersonId)))

FROM #T1 a

But - haven't you just heard this before - why?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden