question

Yorgsneil avatar image
Yorgsneil asked

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

mysqlsql query
1 comment
10 |1200 characters needed characters left characters exceeded

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

/** 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 Likes 0 ·

1 Answer

· Write an Answer
Rickwaldorf avatar image
Rickwaldorf answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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