Urgent help needed with SQL query for scenario described

[saifnawaz434]

This is the scenario:
I have to pick latest Record for following 3 combinations set of data:

first combination:

PC_id P_id P_contact P_name audit_timestamp
1 1 a usa 2005-07-07 07:12:13
2 1 a uk 2005-07-07 07:12:13
3 1 a usa 2005-07-07 07:12:13
---i have to pick PC_id = 3 since audit_timestamp is same i have to go for max (PC_id) so record # 3 will be selected.

but in the same table I have other scenario:

PC_id P_id P_contact P_name audit_timestamp
1 1 a usa 2005-07-07 07:12:13
3 1 a uk 2005-07-07 09:12:13
2 1 a usa 2005-07-07 11:12:13

I have to select PC_ID = 2, since audit timestamp on it is latest.

I have to come up with such a logic so that I pick latest P_name. i know it can be done through Store procedure but it would be great help if some one can help me with select qry for this scenario. Thanks

 

[rtmpxr]

It's not complicated. Order descending by audit_timestamp andPC_id, then select the first record. I don't know what SQL engine you have, so I'll give some examples:

For Firebird:
select first 1 pc_id from <table> order by audit_timestamp desc, pc_id desc

For Microsoft SQL Server:
select top 1 pc_id from <table> order by audit_timestamp desc, pc_id desc

For Oracle:
select pc_id from (select * from <table> order by audit_timestamp desc, pc_id desc) rownum=1

I believe you got the ideea...