Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15

    Question Unanswered: Query for unique and null values

    I am attempting to select one record (the Max(StartDate)) for each ExpertID using the query below. I have manged to do this, but in the event that the only record for an ExpertID has no StartDate, i would like to return it as well. This i cannot seem to figure out.

    SELECT ProjectExperts.ProjectExpertID, ProjectExperts.ProjectID, ProjectExperts.ExpertID, ProjectExperts.StartDate, ProjectExperts.EndDate
    FROM ProjectExperts
    WHERE (((ProjectExperts.StartDate)=(SELECT Max(ProjectExperts_2.StartDate) FROM ProjectExperts AS ProjectExperts_2 WHERE ProjectExperts_2.ExpertID = ProjectExperts.ExpertID)));

    Any suggestions will be greatly appreciated.

    Thanks,

    Justin

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Urrgh!

    I haven't gone into the syntax but I think what may work is the following:

    SELECT .............
    FROM ............
    WHERE ([DATE] = SELECT MAX()...................)
    OR (ISNULL([DATE]) AND COUNT([ID]) = 1 WHERE ...)

    There are two components to the outer where clause. The first is the same as you have already. The second specifies only one occurrence for this expert and the start date is missing. Should work because the two conditions are mutually exclusive.

    Got a feeling it might be quite slow too if you have a big table.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ProjectExpertID
         , ProjectID
         , ExpertID
         , StartDate
         , EndDate
      from ProjectExperts as X
     where StartDate =
         ( select max(StartDate) 
             from ProjectExperts 
            where ExpertID = X.ExpertID )
        or not exists        
         ( select StartDate
             from ProjectExperts 
            where ExpertID = X.ExpertID
              and StartDate is not null )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15
    Thanks - it works perfectly.

Posting Permissions

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