CODEWITHSUNDEEP
sqlms-accessinner-join
Tina Qiaotian Yan
Tina Qiaotian Yan

Reputation: 13

Access SQL sorting the data with latest date

I have this query:

SELECT  distinct  a.Project_Manager, a.Project_ID, a.Topic,  a.Stage,
a.Presentation_Date, a.Presentation_Notes,  a.Presentation_Status,  a.Category,
a.Postponed_Reason,  a.Postponed_Manager
FROM GSC_Presentation_Info  a
inner join   (
           SELECT Project_ID as Project_ID, MAX(Presentation_Date) as
           Max_Presentation_Date
           from GSC_Presentation_Info 
           group by  Project_ID   
              )  b
ON   a.Project_ID = b.Project_ID
and  a.Presentation_Date = b.Max_Presentation_Date

I really want to hide the records which are not linked with a recent presentation date. But Access keeps showing me the circular reference caused by alias Project_ID in query definition's SELECT list. I don't really know how to fix that.

Tried this code:

SELECT Max(GSC_Presentation_Info.Presentation_Date) AS MaxOfPresentation_Date1, GSC_Presentation_Info.Project_ID AS Project_ID
FROM GSC_Presentation_Info
GROUP BY GSC_Presentation_Info.Project_ID;

Works for me, but I need other columns as well. But once I add them in within the group function, they will show the duplicate right away.

Also tried with this code but it shows duplicates too:

SELECT * 
    FROM GSC_Presentation_Info GPI,  
     (SELECT Max(GSC_Presentation_Info.Presentation_Date) AS MaxOfPresentation_Date, GSC_Presentation_Info.Project_ID
     FROM GSC_Presentation_Info
     GROUP BY GSC_Presentation_Info.Project_ID) MVV
    WHERE  GPI.Presentation_Date = MVV.Presentation_Date 
    AND GPI.Project_ID = MVV.Project_ID;

Upvotes: 1

Views: 124

Answers (3)

Lord Peter
Lord Peter

Reputation: 3501

You just need to remove the alias for your Project_ID column - Access won't let you use an alias name that already exists as a column name or another alias name.

...inner join   (
           SELECT Project_ID, MAX(Presentation_Date) as
           Max_Presentation_Date
           from GSC_Presentation_Info 
           group by  Project_ID   
           )  b
...

Upvotes: 1

haldar55
haldar55

Reputation: 79

I think this would work with RANK() function as well. I am using Teradata SQL. The synatx might be slightly different for us.

The table I created:

CREATE TABLE stack_test 
(
 Project_ID VARCHAR(3) CHARACTER SET LATIN CASESPECIFIC,
 Project_Manager VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC,
 Presentation_Date DATE FORMAT 'yyyy-mm-dd'
)
PRIMARY KEY stack_test_pk ( Project_ID ,Presentation_Date );

Records I Inserted:

INSERT INTO stack_test ('123','Adam','2014-05-01');
INSERT INTO stack_test ('123','Adam','2014-05-02');
INSERT INTO stack_test ('123','Adam','2014-05-03');
INSERT INTO stack_test ('234','Leveen','2014-05-03');
INSERT INTO stack_test ('345','Sang','2014-03-01');
INSERT INTO stack_test ('345','Sang','2014-03-02');
INSERT INTO stack_test ('678','Liam','2014-05-19');

SELECT Statement that use:

SELECT 
Project_Manager, 
Project_ID,
Presentation_Date,
RANK() OVER (PARTITION BY Project_ID ORDER BY Presentation_Date DESC) presen_rank
FROM stack_test 
QUALIFY presen_rank = 1;

Result that I got:

Project_Manager       Project_ID  Presentation_Date  presen_rank
--------------------  ----------  -----------------  -----------
Adam                  123                2014-05-03            1
Leveen                234                2014-05-03            1
Sang                  345                2014-03-02            1
Liam                  678                2014-05-19            1

Hope this works for you too.

Since RANK() isn't working for you. Try the following:

SELECT y.Project_Manager, y.Project_ID,y.Presentation_Date
FROM (
        SELECT
            Project_ID,
            MAX(Presentation_Date) AS Presentation_Date
        FROM stack_test 
        GROUP BY Project_ID
     ) x
INNER JOIN stack_test y ON (y.Project_ID = x.Project_ID AND y.Presentation_Date =  x.Presentation_Date)
GROUP BY y.Project_Manager, y.Project_ID,y.Presentation_Date;

This gives the result as follows.

Project_Manager       Project_ID  Presentation_Date
--------------------  ----------  -----------------
Liam                  678                2014-05-19
Sang                  345                2014-03-02
Adam                  123                2014-05-03
Leveen                234                2014-05-03

Upvotes: 0

Torrezzzz
Torrezzzz

Reputation: 307

Try this

SELECT Project_Manager, Project_ID, Topic, Stage, Presentation_Date, Presentation_Notes, Presentation_Status, Category, Postponed_Reason, Postponed_Manager FROM GSC_Presentation_Info
group by Project_ID order by Presentation_Date desc limit 5

This will produce the latest 5 dates results. If you want more than that just increase limit.

Upvotes: 0

Related Questions