writing "Comparator" in SQL

Question

Hello,

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

Answer 1

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.

Answer 2

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.