1

I have a table with the following sample structure:

Identifier| Latitude | Longitude |...many columns...|DateWhenStatusObserved|ID|
----------+----------+---------- +------------------+----------------------+--+
2823DC012 | 28.76285 | 23.70195  |  ...             |            1994/10/28| 1|
2823DC012 | 28.76285 | 23.70195  |  ...             |            1995/04/05| 2|
2822DD030 | 28.76147 | 22.98270  |  ...             |                  NULL| 3|
...

There are many more columns, but these columns do not have to be evaluated, all columns should just be returned from the query.

I would like the SQL query to return only unique records for the Identifier column with the latest date per unique Identifier. Unfortunately there are also records were date is NULL in the DateWhenStatusObserved column and in many instances the only record for an Identifier (geosite) has a NULL date.

There are already many answers for similar SQL questions such as:

How can I include null values in a MIN or MAX?

SELECT only rows with either the MAX date or NULL

http://bytes.com/topic/access/answers/719627-create-query-evaluate-max-date-recognizing-null-high-value

These are however not specific on how exactly does one use the iif statement with an aggregate Max function to allow the NULL date records to pass through while maintaining unique identifier (geosite) records.

I only get non-NULL max date records returned using a subquery and combination of Max(IIF()). I finally got a reasonable result from a basic subquery without Joins and relied on WHERE clauses, but I get duplicate identifier records from NULL dates, because I have to use OR instead of AND to get any rows returned.

Here is one of my attempts returning only non-NULL max date records:

SELECT BasicInfoTable.*
FROM Basic_information_WUA AS BasicInfoTable 
INNER JOIN 
(
SELECT Identifier, MAX (IIF(DateWhenStatusObserved IS NULL, 0, DateWhenStatusObserved)) AS MaxDate 
FROM Basic_information_WUA
GROUP BY Identifier
)  
AS Table2 ON BasicInfoTable.Identifier = Table2.Identifier AND BasicInfoTable.DateWhenStatusObserved = Table2.MaxDate;

So why is this not working for the NULL date cases?

I would appreciate any help with finding the near-optimal query for this problem.

Thanks

Community
  • 1
  • 1
Reuben
  • 13
  • 4
  • You need to provide similar (is NULL) logic to BasicInfoTable.DateWhenStatusObserved = Table2.MaxDate. Nulls cannot be "compared". – Anthony Horne Mar 17 '15 at 20:21
  • @AnthonyHorne - Thank you your suggested edit has the query running perfectly. The query returns unique Identifier field records, latest(max) dates and null dates in cases where there are no dates. The change then results in: `ON (IIF(BasicInfoTable.DateWhenStatusObserved IS NULL, 0,BasicInfoTable.DateWhenStatusObserved) = IIF(Table2.MaxDate IS NULL, 0, Table2.MaxDate)`. Anthony, unfortunately I can't vote your answer up because its only in comments. – Reuben Mar 18 '15 at 09:26
  • Glad it's working for you. Another set of eyes is often all that is needed. – Anthony Horne Mar 18 '15 at 10:39
  • 1
    I've accidentally left out a closing bracket/parentheses at the end of my code section in my comment above, but I can't edit it. Just keep in mind. – Reuben Mar 18 '15 at 12:01

1 Answers1

0

You need to provide similar (is NULL) logic to BasicInfoTable.DateWhenStatusObserved = Table2.MaxDate. Nulls cannot be "compared".

Anthony Horne
  • 2,522
  • 2
  • 29
  • 51