Urgent help needed with SQL query for scenario described

saifnawaz434 · Mar 10, 2006

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 · Mar 13, 2006

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...

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Urgent help needed with SQL query for scenario described

saifnawaz434

Computer

Recommended for you

rtmpxr

Nuclear

Similar threads

Part and Inventory Search

Sponsor