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)