New post
Question
· Feb 15, 2022

Select top field in SQL query

#Ensemble

Hi Guys,

I've the below query that selects the existing processes with dates & times and it working fine, but in case of records with the same processedDate I would like to only get the latest based on processedTime field?

basically, bellow is and example of my query currently produce, where records 2 & 3 are the similar with different times   :

1,  record1 ,10/02/2022 ,13:40:00

2,record2,11/02/2022, 14:11:00

3,record2,11,02,2022,16:00:00

4,record3,12,02,22 , 11:00:00

and in this case I would like my query to get the latest record in time to achieve the following:

 

1,  record1 ,10/02/2022 ,13:40:00

2,record2,11,02,2022,16:00:00

3,record3,12,02,22 , 11:00:00

 

"Select ID, name, processedDate, processedTime  From  MYprocesses where NewQty is not null group by name,processedDate " 

 

Thanks

Product version: Ensemble 2014.1
Discussion (2)1
Log in or sign up to continue
Eduard Lebedyuk · Feb 15, 2022

Something like this?

SELECT
    ID,
    name,
    processedDate,
    processedTime
FROM MYprocesses m
WHERE NOT EXISTS (  SELECT 1
                    FROM MYprocesses m1
                    WHERE 1=1
                        AND m1.name=m.name
                        AND TO_POSIXTIME(m1.processedDate||' '||m1.processedTime, 'yyyy/mm/dd hh:mm:ss') > TO_POSIXTIME(m.processedDate||' '||m.processedTime, 'yyyy/mm/dd hh:mm:ss'))

Replace TO_POSIXTIME with TO_TIMESTAMP on older versions.

If record ids are aligned with time (meaning higher id has higher processed date/time) you can simplify and speed up the query:

SELECT
    ID,
    name,
    processedDate,
    processedTime
FROM MYprocesses m
WHERE NOT EXISTS (  SELECT 1
                    FROM MYprocesses m1
                    WHERE 1=1
                        AND m1.name=m.name
                        AND m1.id>m.id)
0 0