SQL Sybase ASE - select most recent date and max price

Question

I am working on Adaptive Server Enterprise/15.7/EBF 25127 SMP SP136 /P/x86_64/Enterprise Linux/ase157sp136x/3955/64-bit/FBO/Mon Oct 19 19:12:00 2015

I have a table that includes records with multiple dates and prices, and I am looking to select the most recent date AND the price that is the highest or max.

For error messages, the max() function doesn't seem to work and just returns hundreds of thousands of records. When I try Row_number() or Rank() OVER (Partition by columnname order by columnname) I receive a syntax error near OVER is incorrect. Upon reading other threads, it looks like this is not available in Sybase ASE.

Is there another way to accomplish this?

Example of one record in table:

ID     TRADE_DATE  TRADE_PRICE
B52A   05/12/2016  91
B52A   05/13/2016  85
B52A   05/13/2016  86
B52A   05/16/2016  85
B52A   05/16/2016  90

Expected Output (just one row per record, no duplicates):

ID     TRADE_DATE  TRADE_PRICE
B52A   05/16/2016  90

My code:

SELECT T1.ID
FROM T1

LEFT JOIN
(SELECT
  T2.ID,
  T2.TRADE_DATE,
  T2.TRADE_PRICE
FROM T2
WHERE ID='B52A')
ON T2.ID=T1.ID

Also, if I try doing:

SELECT
ID,
MAX(TRADE_DATE),
MAX(TRADE PRICE)

FROM T2
WHERE ID ='B52A'

GROUP BY
  ID
ORDER BY
  ID
  TRADE_DATE
  TRADE_PRICE

It selects the correct record, but it repeats it 5 times:

ID     TRADE_DATE  TRADE_PRICE
B52A   05/16/2016  90
B52A   05/16/2016  90
B52A   05/16/2016  90
B52A   05/16/2016  90
B52A   05/16/2016  90

Answer 1

You can not use the OVER syntax since the SAP/Sybase ASE database does not support it.

You need to write the SQL code manually. One example is:

SELECT
  T1.ID, x.max_TRADE_DATE, max(TRADE_PRICE) AS max_TRADE_PRICE
FROM 
(
  SELECT ID, MAX(TRADE_DATE) AS max_TRADE_DATE
  FROM T1 GROUP BY ID
) x 
JOIN
  T1 on T1.ID = x.ID and T1.TRADE_DATE = x.max_TRADE_DATE
GROUP BY
  T1.ID, x.max_TRADE_DATE

Answer 2

SELECT distinct * FROM (SELECT
ID,
MAX(TRADE_DATE) trade_date,
MAX(TRADE PRICE) trade_price

FROM T2
WHERE ID ='B52A'

GROUP BY
  ID
)
ORDER BY
  ID,
  TRADE_DATE,
  TRADE_PRICE