SQL Query find maximum date for a set of records

Question

question

Yorgsneil asked May 28, '21

SQL Query find maximum date for a set of records

First off .. new at this ...

I have a sql query against a single table let call it employee, and the employee can work in multiple divisions over time.

Given the data set below in Fig 1 I want to create a table that returns the following .

I want the Maximum date and the division for each unique employee_id ( Fig 2 )

I tried some things but they did not work.

Thanks in advance for your assistance !

( Fig 2 )

employee_id division file_dt
77308036 DVHR 2020-01-30
77312457 DV0063 2021-05-26
77382856 DV0056 2021-05-26

( Fig 1 )

employee_id division file_dt
77308036 DVHR 2020-01-30
77312457 DV0063 2021-05-26
77312457 DV0066 2019-05-09
77312457 DV0058 2019-05-09
77382856 DV0056 2021-05-26
77382856 DV0063 2019-05-09
77382856 DVEIM 2019-05-09

mysql sql query

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yorgsneil commented · May 27, 2021 at 08:14 PM

/** I only want to gather records if the are later than file_dt of 2019-10-25

SELECT

employee_id,

division,

TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(TRIM(employee.file_dt), 'MM-dd-yyyy'))) as file_dt

FROM employee

WHERE

MAX( TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(TRIM(employee.file_dt), 'MM-dd-yyyy'))) ) =

TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(TRIM(employee.file_dt), 'MM-dd-yyyy')))

AND TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(TRIM(employee.file_dt), 'MM-dd-yyyy'))) >= '10-25-2019' ;

;

0 ·

Answer 1 · 2021-06-01T18:30:24Z

Rickwaldorf answered Jun 1, '21

Might try this ..

with tmpfile as (

select fig1.field1, fig1.field2,fig1.field3, ROW_NUMBER() over (partition by [fig1.field1] order by [fig1.field1],[fig1.field3] desc ) as [row number]

from fig1 where fig1.field3 >= '201901025')

insert into NEW_FILE select * from tmpfile where tmpfile .[row number] = 1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question