Last, Group By, unique results

[Yair]

Greetings,

I'm having trouble getting unique results from the a query which uses the
"last of" a date column and any other fields. Whenever I select another
field, multiple records appear for each entry.

In the simplest case, I have two tables that are used in the query:

Projects
ScheduleSnapshot

Projects is primary keyed by ProjectTitle, which is the foreign key in the
ScheduleSnapshot table. The ScheduleSnapshot table has a DateRealized
columnt which indicates the date the record was entered.

Here is the query:

SELECT DISTINCTROW ScheduleSnapshot.ProjectTitle,
Last(ScheduleSnapshot.DateRealized) AS LastOfDateRealized,
ScheduleSnapshot.ContractorsEstCompDate
FROM Projects INNER JOIN ScheduleSnapshot ON Projects.ProjectTitle =
ScheduleSnapshot.ProjectTitle
GROUP BY ScheduleSnapshot.ProjectTitle,
ScheduleSnapshot.ContractorsEstCompDate;

I would like this query to return 1 record for each ProjectTitle, with the
contractors completion date a unique value corresponding to the last date
realized, but the query returns completion dates for each date realized.

Thanks for the help.

 

[Marshall Barton]

I'm having trouble getting unique results from the a query which uses the
"last of" a date column and any other fields. Whenever I select another
field, multiple records appear for each entry.

In the simplest case, I have two tables that are used in the query:

Projects
ScheduleSnapshot

Projects is primary keyed by ProjectTitle, which is the foreign key in the
ScheduleSnapshot table. The ScheduleSnapshot table has a DateRealized
columnt which indicates the date the record was entered.

Here is the query:

SELECT DISTINCTROW ScheduleSnapshot.ProjectTitle,
Last(ScheduleSnapshot.DateRealized) AS LastOfDateRealized,
ScheduleSnapshot.ContractorsEstCompDate
FROM Projects INNER JOIN ScheduleSnapshot ON Projects.ProjectTitle =
ScheduleSnapshot.ProjectTitle
GROUP BY ScheduleSnapshot.ProjectTitle,
ScheduleSnapshot.ContractorsEstCompDate;

I would like this query to return 1 record for each ProjectTitle, with the
contractors completion date a unique value corresponding to the last date
realized, but the query returns completion dates for each date realized.

The key problem here is that you've misinterpreted the
purpose of the Last function. It only returns the value
from the last record in the query's input dataset. Since
tables are inherently unordered, you will get an essentially
random result from Last.

Most likely, you want to use the Max function to get the
latest date for each project.

 

[Yair]

Thanks Marsh.

I switched to Max, since indeed I misinterpreted Last.

However, the same problem persists.

I need projectTitle, completionDate, selected for each projectTitle, ONLY
for the max dateRealized.

For example,

ProjectTitle EstCompDate DateRealized

Starbucks 11/11/05 9/13/05
Starbucks 11/15/05 8/29/05
Wendy's 12/01/05 9/12/05
Wendy's 12/09/05 9/1/05

The query would return:

Starbucks 11/11/05 9/13/05
Wendy's 12/01/05 9/12/05

instead it returns everything.

Seems like this should be easy to do and that this question is silly, but
I'm stuck.

Thanks.

 

[Marshall Barton]

I switched to Max, since indeed I misinterpreted Last.

However, the same problem persists.

I need projectTitle, completionDate, selected for each projectTitle, ONLY
for the max dateRealized.

For example,

ProjectTitle EstCompDate DateRealized

Starbucks 11/11/05 9/13/05
Starbucks 11/15/05 8/29/05
Wendy's 12/01/05 9/12/05
Wendy's 12/09/05 9/1/05

The query would return:

Starbucks 11/11/05 9/13/05
Wendy's 12/01/05 9/12/05

instead it returns everything.

Not a simple question at all. Try something like:

SELECT T.ProjectTitle, T.EstCompDate, T.DateRealized
FROM thetable As T
WHERE T.DateRealized = (SELECT Max(X.DateRealized)
FROM thetable As X
WHERE X.ProjectTitle = T.ProjectTitle)

 

[Yair]

Bless you, this works -- I now need to understand what you wrote.

I'm not sure why I need to give the table an alias (T, X) in order to
perform the query. I had no idea it was possible to put a query in the
criteria field of a column.

This query now looks completely mystifying in the query builder but I'm
gonna roll with it and see if I can't finish it.

Thanks!

 

[Yair]

Ok, just a few questions to make sure I understand what is going on with
this query:

1. The query wasn't working before because I was using "group by" in the
total field. Now I use "expression" which prevents that problem.

2. I need to use an alias for the table names in order to use "expression"
in the total field.

3. It is possible to use "expression" in a column even though there is no
actual "expression" (i.e., a criteria) that is being evaluated.

Again, thanks so much.

 

[Marshall Barton]

You don't need the T alias, it just makes the rest of the
query easier to type and read. The X (or whatever) alias is
required so you can distinguish which instance of a field
you are referring to.

The basic idea is fairly simple, you only want the records
where the DateRealized field matches the latest one. The
subquery retrieves the latest DateRealized so the main query
can throw away all the records with an earlier date.

 

[Marshall Barton]

You've lost me. Where does a Group By clause come into this
question? Are you doing something beyond the query I
posted?

Maybe you need to start over with a copy of the query you
are now using and an explanation of what the goal is at this
point.