Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Equivalent of FIRST Keyword in Grouping

    Hi All

    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).

    Code:
    
    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
     
    (SELECT Entity_Ref 
    FROM 
    ATTR_DEF 
     
    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
    '')'
    
    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

    Any Ideas ?

    Thanks
    GW
    "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

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Mmmmmm - Not much Joy with this one then ;-( - I hope it's nothing I've said.

    I eventualy resolved by deleting extranuous records from the #temp table that I was populating with the INSERT #temp EXECUTE @SQL.

    There are just some times That SELECT TOP 1 does'nt replace First as an option

    I Could have used something like below but thought a straight delete would be quicker and leave me with a valid working #table.

    Code:
     
    SELECT t.*
    FROM #temp t
    WHERE t.PKey IN (SELECT TOP 1 PKey FROM #temp WHERE #temp.PKey=t.PKey 
    ORDER BY [whatever])
    Better luck next time hopefuly

    GW
    "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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was going to suggest TOP 1...
    Quote Originally Posted by GWilliy
    There are just some times That SELECT TOP 1 does'nt replace First as an option
    Can you elaborate on this at all? Possibly some sample data, expected result and actual result?
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Oooops

    Well I guess I was just thinking of performance as in this case I have around 50,000 records with only a very very small percentage that have two or more residents in a dwelling (OAP Sheltered Housing)

    I ended up needing to capture Total number of Residents with a @@ROWCOUNT anyway.

    I will willingly concede The FIRST Keyword can ALWAYS be replaced as efficiently with an IN (SELECT TOP 1 ..... if that's your understanding georgev.

    Apologies for the Flippant Remark my mind was Fuzzed with the joys of Dynamic SQL, SubCategorized Schema's & Dynamic linked Servers

    GW
    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •