Urgent help needed with SQL query for scenario described

Question

Urgent help needed with SQL query for scenario described

saifnawaz434 (Computer)

(OP)

10 Mar 06 20:59

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 (Nuclear) · Answer 1 · 2006-03-13 05:42:17

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

Come Join Us!

Posting Guidelines

Urgent help needed with SQL query for scenario described