Access SQL to return only first or last occurence by date

Question

First of all, I don't have many experiences with SQL and I have spent a lot of time trying to make it work. Please help.

I have original table from access database which contains columns: date, time, EP (point of entry), ID and rest of columns are just informational. No primary key, Change of table is last solution.

DateOS      TimeOS     EP    ID    Val1    Val2    Val3     Val4
1.2.2017    1:00:02    in    15    6280    blue    line1    s
1.2.2017    3:00:06    in    15    6280    blue    line1    s
1.2.2017    4:00:08    in    16    3147    red     line2    s
1.2.2017    5:00:10    out   20    6280    white   line3    c
1.2.2017    6:00:12    out   20    6280    white   line3    c
2.2.2017    2:00:04    in    16    3147    red     line2    s

I need to get only first (green in expected result)

DateOS      TimeOS     EP    ID    Val1    Val2    Val3     Val4
1.2.2017    1:00:02    in    15    6280    blue    line1    s
1.2.2017    4:00:08    in    16    3147    red     line2    s
1.2.2017    5:00:10    out   20    6280    white   line3    c

/ last (red in expected result) occurences that have unique combination of EP and ID.

DateOS      TimeOS     EP    ID    Val1    Val2    Val3     Val4
1.2.2017    3:00:06    in    15    6280    blue    line1    s
1.2.2017    6:00:12    out   20    6280    white   line3    c
2.2.2017    2:00:04    in    16    3147    red     line2    s

I got ideas from: Get the first instance of a row using MS Access

So far I have this (min function for first / max function for last):

SELECT
    FORMAT(pt.DateOS, 'dd.MM.yyyy') AS DateOS, 
    FORMAT(pt.TimeOS, 'HH:mm:ss') AS TimeOS, 
    pt.EP, pt.ID, pt.Val1, pt.Val2, pt.Val3, pt.val4
FROM [test07_dupl2] AS pt 
WHERE TimeOS 
IN
(
SELECT
    MIN(TimeOS) AS MinDateTimeOS 
FROM [test07_dupl2]
GROUP BY EP+ID
)

And this is result. It is considering just TimeOS. Second line does not belong there, because it is later date.

DateOS      TimeOS     EP    ID    Val1    Val2    Val3     Val4
1.2.2017    1:00:02    in    15    6280    blue    line1    s
2.2.2017    2:00:04    in    16    3147    red     line2    s
1.2.2017    5:00:10    out   20    6280    white   line3    c

The problem is that I need to consider DateOS + TimeOS. So I tried to write MIN(DateOS + TimeOS) and also create new column with value DateOS + TimeOS but both returned empty table. I'm not sure if I made some mistakes, or this is wrong approach.

score 1 · Accepted Answer · answered Sep 25 '18 at 10:56

One method for handling this is to check that no records exist before the given record. This uses NOT EXISTS.

The logic is a bit complicated because the date/time is in two different columns. But for the first record:

SELECT pt.*
FROM [test07_dupl2] AS pt 
WHERE NOT EXISTS (SELECT 1
                  FROM [test07_dupl2] AS pt2
                  WHERE pt2.EP = pt.EP AND pt2.ID = pt.ID AND
                        (pt2.DateOS < pt.DateOS OR
                         pt2.DateOS = pt.DateOS AND pt2.TimeOS < pt.TimeOS
                        )
                 );

For the last, the final condition would be:

                        (pt2.DateOS > pt.DateOS OR
                         pt2.DateOS = pt.DateOS AND pt2.TimeOS > pt.TimeOS
                        )

It's working perfectly! It's funny that it took me 15min to compose this post and you have right aswer with blink of an eye. — Stana Macala, Sep 25 '18 at 11:05
@StanaMacala . . . It actually wasn't that fast ;) MS Access is rather limited in its functionality compared to other databases, so I actually had to think about this one. — Gordon Linoff, Sep 25 '18 at 12:34

score 0 · Answer 2 · answered Sep 26 '18 at 07:42

I have added DISTICT function to code from @Gordon Linoff to make sure that also duplicate lines with same date and time are gone.

SELECT DISTINCT t.*
FROM[SIMASSIST].[dbo].[TestDupl] AS t
WHERE
NOT EXISTS
(
    SELECT 1
    FROM[SIMASSIST].[dbo].[TestDupl] AS t2
    WHERE t2.EP = t.EP AND t2.ID = t.ID AND
    (
        (t2.DateOS < t.DateOS OR t2.DateOS = t.DateOS) AND 
        (t2.TimeOS < t.TimeOS)
    )
)

Access SQL to return only first or last occurence by date

2 Answers2

Linked