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

    Unanswered: SELECT queries and unique values

    For the purpose of tracking projects and the dates at which they attain certain stages I have a the following table

    ProjectID - ID number for the project record
    StageID - ID number for the stage
    Date - The date the stage was attained

    I have been able to get the latest or current stage for each project by using the following query:

    SELECT TOP 1 ProjectStages.ProjectID, ProjectStages.StageID, ProjectStages.StageDate
    FROM ProjectStages
    WHERE (((ProjectStages.ProjectID)=101))
    ORDER BY ProjectStages.StageDate DESC;

    Now I need a query to list every project and it's current stage. How can I write I query to only select one record (with the lastest date) for each ProjectID?

    Any help will be greatly appreciated.

    Thanks,
    Justin

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select t.projectID, t.stage, t.date
    from table t
    where t.date = (select max(date) from table where projectID = t.projectID);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15
    Thanks, but the query is only giving me the one record with lastest date.

    SELECT ProjectStages.ProjectID, ProjectStages.StageID, ProjectStages.StageDate
    FROM ProjectStages
    WHERE (((ProjectStages.StageDate)=(select max([stagedate]) from ProjectStages where projectID =ProjectStages.projectID)));

    I need the lastest date for each ProjectID. How can I get it to list one record for each ProjectID?

    Thanks,
    Justin

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Could you please further explain the result that you require.
    Code:
    SQL> select * from test;
    
    NAME       CITY                        NUM
    ---------- -------------------- ----------
    personA    CityA                         2
    personA    CityB                         4
    personB    CityC                         5
    personB    CityD                         6
    
    SQL> select t.name, t.city, t.num
      2  from test t
      3  where t.num = (select max(num) from test where name = t.name);
    
    NAME       CITY                        NUM
    ---------- -------------------- ----------
    personA    CityB                         4
    personB    CityD                         6
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    You need to modify your query slightly, Justin84. The query should read:

    SELECT ProjectStages.ProjectID, ProjectStages.StageID, ProjectStages.StageDate
    FROM ProjectStages
    WHERE ProjectStages.StageDate=(SELECT Max(ProjectStages_2.StageDate) FROM ProjectStages AS ProjectStages_2 WHERE ProjectStages_2.ProjectID=ProjectStages.projectID;


    Try this and see how it works.

  6. #6
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15
    Works perfectly.
    Thanks.

Posting Permissions

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