I have a table that contain approval transaction data, the data is created whenever user submit a request that require an approval.

Here how the data look's like more or less:

IdApprover IdIsCompleteIsApproveCreatedDate
145truefalse2019-9-17 11:00:05
246truefalse2019-9-17 11:00:05
345truetrue2019-9-17 14:00:00
446falsefalse2019-9-17 14:00:00

As you can see, the user submit two request where the first request is submitted on 11am and declined by the approver, and the second request submitted on 2pm with one of the approver already approve while the other one not yet.


What I want to do is to display last set of approval transaction, based on the data bove I want only Id 3 and 4 displayed in the screen. Group By Created Date might do the trick but then I can only display the CreatedDate field in the screen.
Solution

Sorry but can you please elaborate a bit more on the query. I am just a bit confused. 


Are we missing a request ID field. If we have that, then it will be simply taking the latest records for that request ID. 

But even in the current scenario, what you are trying to achieve can be done by applying a self join and then selecting the records based on the grouping in the second table. 


Or a simple query which says, 


Select * from tbl where created_date in (Select top 1 created_date from tbl group by created date order by created date desc) 


There can be multiple more optimized ways. But this is just to give you an idea. 


Cheers. 


Solution

hi,

what dbms are you using?

For Oracle Advanced QUery:

https://dba.stackexchange.com/questions/6368/how-to-select-the-first-row-of-each-group


When using SQL server as database, then you could use the OVER clause to solve your problem.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

Hi Kevin,

Here's a very crude primitive approach, but I hope it helps to achieve what you want to do.


I've attached a sample OAP for you to try out.

Please let me know if it works.

Thanks & regards,

Boney


Thanks for the answers guys!