Unanswered: Equivalent of FIRST Keyword in Grouping
I should probably know the answer to this after all my time with TSQL But my brain seems to have run out of steam. Have you ever tried Googling an answer for "FIRST Keyword equivalent" D'oh.
Essentially I'm looking for an alternative to the SyBase/Access/etc FIRST Keyword
Anyway I have
LocationRef (Many Locations) PKey
ResidentRef (a location has one or more Residents) PKey
SP_Flag (I only want locations that have a Resident with an SP_Flag set in it)
Primary_YN (a resident may or not be a Primary)
I want to Return only one Location & one Resident per Location
(Preferably the one that is Primary OR one that is not Primary so long as the they have the SP_Flag Set)
If we had a FIRST Keyword I would Simply SELECT FIRST and Order By Primary having removed locations without a resident with SP_Flag set - Job done.
The same Statement must run against both SyBase ASA & SQL Sever via Linked servers & OPENQUERY hence my frustration.
I include the statement as it stands at the moment for completeness & to show I'm dealing with the complexities of a Crap & SubCategorized Schema (naturally not of my doing).
Note: The Inner Join does'nt work for me because it excludes locations that have a resident with an SP_Flag but they are not a primary
SET @SQL ='SELECT * FROM OPENQUERY(Client3,''
SELECT r1.LOCATION_REF LocRef, r1.Resident_Def ResRef,r1.Title,r1.First_Name, r1.Last_Name, r1.Date_Of_Birth, r1.Primary_YN
FROM Resident r1
INNER JOIN Resident r2 ON r1.Resident_Def = r2.Resident_Def AND r2.Primary_YN = ''''Y''''
WHERE r1.Resident_Def IN
INNER JOIN ATTR_CHOICE ON
ATTR_DEF.ATTR_CHOICE_REF = ATTR_CHOICE.ATTR_CHOICE_DEF
AND ATTR_DEF.ENTITY_TYPE = 5
INNER JOIN ATTR_CATEGORIES ON
ATTR_CHOICE.ATTR_CATEGORY_REF = ATTR_CATEGORIES.ATTR_CATEGORY_DEF
INNER JOIN RESIDENT ON
ATTR_DEF.ENTITY_REF = RESIDENT.RESIDENT_DEF
WHERE UPPER(ATTR_CATEGORIES.TEXT) = ''''SP USER''''
AND UPPER(ATTR_CHOICE.TEXT) = ''''YES''''
AND RESIDENT.Date_Of_death IS NULL)
ORDER BY 1
Any Ideas ?
"Everything should be made as simple as possible, but not simpler." - Albert Einstein
"Everything should be made as complex as possible, so I look Cleverer." - Application Developer