Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Urgent help needed with SQL query for scenario described

Status
Not open for further replies.

saifnawaz434

Computer
Mar 10, 2006
1
US
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


 
Replies continue below

Recommended for you

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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top