CODEWITHSUNDEEP
sqlms-accessgroup-byduplicates
user3301926
user3301926

Reputation: 1

How to Avoid Duplicate ID's In Access SQL

I have a problem that I hope you can help me.

I have the next query on Access SQL.

SELECT 
    ID_PLAN_ACCION, ID_SEGUIMIENTO, 
    Max(FECHA_SEGUIMIENTO) AS MAX_FECHA
FROM 
    SEGUIMIENTOS
WHERE 
   (((ID_PLAN_ACCION) = [CODPA]))
GROUP BY 
   ID_PLAN_ACCION, ID_SEGUIMIENTO;

And it returns this information:

ID_PLAN_ACCION  ID_SEGUIMIENTO  MAX_FECHA
-----------------------------------------------    
A1-01                  1             16/01/2014
A1-01                  2             30/01/2014

But I really need that it throws off this:

ID_PLAN_ACCION  ID_SEGUIMIENTO  MAX_FECHA
----------------------------------------------    
A1-01                  2             30/01/2014

As you can see I only need the record that has the most recently date, not all the records

The GROUP BY doesn't work.

Please can you tell me what can I do? I am new on all this.

Thank you so much!!

PD: Sorry for my english, I'm learning

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This will produce the results you want:

SELECT ID_PLAN_ACCION, max(ID_SEGUIMIENTO) as ID_SEGUIMIENTO, Max(FECHA_SEGUIMIENTO) AS MAX_FECHA
FROM SEGUIMIENTOS
WHERE  ID_PLAN_ACCION = [CODPA]
GROUP BY ID_PLAN_ACCION;

I removed id_sequiimiento from the group by and added an aggregation function to get the max value. If the ids increase along with the date, this will work.

Another way to approach this query, though, is to use top and order by:

SELECT top 1 ID_PLAN_ACCION, ID_SEGUIMIENTO, FECHA_SEGUIMIENTO
FROM SEGUIMIENTOS
WHERE  ID_PLAN_ACCION = [CODPA]
ORDER BY FECHA_SEGUIMIENTO desc;

This works because you are only returning one row.

EDIT:

If you have more codes, that you are looking at, you need a more complicated query. Here is an approach using where/not exists:

SELECT ID_PLAN_ACCION, ID_SEGUIMIENTO, FECHA_SEGUIMIENTO
FROM SEGUIMIENTOS s
WHERE  not exists (select 1
                   from SEGUIMIENTOS s2
                   where s.ID_PLAN_ACCION = s2.ID_PLAN_ACCION and
                         s2.FECHA_SEGUIMIENTO > s.FECHA_SEGUIMIENTO
                  )
ORDER BY FECHA_SEGUIMIENTO desc;

You can read this as: "Get me all rows from SEGUIMIENTOS where there is no other row with the same ID_PLAN_ACCION that has a larger date". Is is another way of saying that the original row has the maximum date.

Upvotes: 2

Related Questions