How to do SQL join effectively?

I have two tables.

1. Order
2. Replication.

A single order record can have multiple Replication records. I want to join these two tables, such that i always retrieve a single record out of the join even if multiple records exist. Sample data

Replication table:
ORDID     | STATUS | ID | ERRORMSG | HTTPSTATUS | DELIVERYCNT
=========================================================

1717410307      1   JBM-9e92ae0c  NULL       200   1 
开发者_如何学C----------
1717410307      1   JBM-9fb59af1  NULL       400   -99
----------
1717410308      1   JBM-0764b091  NULL       403   1
----------
1717410308      1   JBM-0764b091  NULL       200   1

---

Order Table:

ORDID | ORDTYPE | DATE
----------
1717410307  CAR 22-SEP-2011
1717410308  BUS 23-SEP-2011

How can i make a join effectively so as , i will get as many records in order table and a replication table that should be dynamically selected on a priority basis.

The priority can be defined as :

1. Any record with a delivery count of -99
2. HTTPSTATUS != 200

Please guide me how can i proceed with this joining?

Please let me know if you need any clarification.

Your help is much appreciated!

---

Is it possible to use ORDER BY clause based on the HTTPSTATUS and DELIVERYCNT?

In that case you can write a specific ORDER BY and getting the TOP 1 from it (don't know which RDBMS do you use) or getting ROW_NUMBER() OVER (ORDER BY ... ) AS RowN WHERE RowN = 1 But this is the ugly (yet quick) solution.

The other option is to make a subquery where you add a new column which will make the priority calculation.

To make the query effective you should consider indexing (or using RDBMS specific solutions like included columns)