writing "Comparator" in SQL

I have the following problem:

find the highest row in a table A according to the following rules:

Table A

Columns: V_Date Date, Type int, H_Date Date

1) 开发者_如何学JAVAfind the highest V_Date

2) if V_Dates are the same find the row with the highest Priority, where Priority is defined in Table B with columns Type int, Priority int

3) if V_Date and Priority are the same, find the one with the highest H_Date (then it is guaranteed to be unique)

The priorities are not distinct, so max (prio) returns more than one value.

Can anybody help me?

Thank you very much.

Use ORDER BY and limit the result to one row:

SELECT *
FROM TableA
JOIN TableB ON TableA.Type = TableB.Type
ORDER BY V_Date DESC, Priority DESC, H_DATE DESC
LIMIT 1

Exact syntax may vary depending on the specific database.

• In MySQL and PostgreSQL you can use LIMIT 1 as above.
• In SQL Server you can use SELECT TOP(1).
• In Oracle you can use SELECT * FROM (subquery here) WHERE rownum = 1.

Sounds like homework (if it isn't, tell me). So you'll get a general answer. More detail might require you to specify the db type.

JOIN the tables. Use the ORDER BY statement in combination with the TOP statement.