The max value of several columns (date and cost relationship)

How do you return the max value of several columns (date and cost relationship)?

TableName [ID, Date1, Cost1, Date2, Cost2, Date3, Cost3]

I need to return something like this: [ID, Most Recent Date, Cost]

I need to return something like this:

• ID
• Most Recent Date
• the Cost related to the Date (if the most recent date is Date1 so the Cost is Cost1)

I need to use another table - BackupTableName:

• ID
• Date1
• Cost1
• Date2
• Cost2
• Date3
• Cost3

...and use its' data in the situation when Cost1, Cost2 and Cost3 from TableName are n开发者_开发技巧ull.

SELECT ID,
    CASE
        WHEN Date1>Date2 AND Date1>Date3 THEN Date1
        WHEN Date2>Date1 AND Date2>Date3 THEN Date2
        ELSE Date3
    END AS "Most Recent Date",
    CASE
        WHEN Date1>Date2 AND Date1>Date3 THEN Cost1
        WHEN Date2>Date1 AND Date2>Date3 THEN Cost2
        ELSE Cost3
    END AS Cost
FROM TableName;

‘Date1’...‘Date2’...‘Date3’ is a schema smell. Consider normalising to a separate many-to-one table.

ETA:

in the situation when Cost1, Cost2 and Cost3 from TableName are null.

You have nulls in there too? OK, this is getting really unpleasant. You'd need to protect against the nulls, like:

    CASE
        WHEN Date1>COALESCE(Date2, 0) AND Date1>COALESCE(Date3, 0) THEN Date1
        WHEN Date2>COALESCE(Date1, 0) AND Date2>COALESCE(Date3, 0) THEN Date2
        ELSE Date3
    END AS "Most Recent Date",

(in both the date-selection bit and the cost-selection bit. This assumes a Cost will always normally be higher than 0.)

I need to use another table: BackupTableName [ID, Date1, Cost1, Date2, Cost2, Date3, Cost3] and use its data

Well... it's possible but your model is now looking extremely precarious.

SELECT t0.ID,
    CASE
        WHEN t0.Date1>COALESCE(t0.Date2, 0) AND t0.Date1>COALESCE(t0.Date3, 0) THEN t0.Date1
        WHEN t0.Date2>COALESCE(t0.Date1, 0) AND t0.Date2>COALESCE(t0.Date3, 0) THEN t0.Date2
        WHEN t0.Date3>COALESCE(t0.Date1, 0) AND t0.Date3>COALESCE(t0.Date1, 0) THEN t0.Date3
        WHEN t1.Date1>COALESCE(t1.Date2, 0) AND t1.Date1>COALESCE(t1.Date3, 0) THEN t1.Date1
        WHEN t1.Date2>COALESCE(t1.Date1, 0) AND t1.Date2>COALESCE(t1.Date3, 0) THEN t1.Date2
        WHEN t1.Date3>COALESCE(t1.Date1, 0) AND t1.Date3>COALESCE(t1.Date1, 0) THEN t1.Date3
    END AS "Most Recent Date",
    -- the same thing all over again for the cost selector
FROM TableName AS t0
LEFT JOIN BackupTableName AS t1 ON t0.ID=t1.ID;

If it is at all within your power you need to normalise these tables, because the data model you have at the moment is essentially broken.

If your DB supports, you can use greatest function

select ID, greatest(Date1, Date2, Date3) as recent_date, 
           greatest(Cost1, Cost2, Cost3) as priceless from Table