SQL | Criteria Query for max value in a subquery

Question

I need to find out the row corresponding to the row in table2 which gives a single row output for the below query. I will be writing a JPA criteria query for this query. I am able to get the max(date) from the subquery but not the exact row (corresponding to this max value) for the table2.

SELECT table2.ER AS col_0_0_,
       table1.CR AS col_1_0_
FROM table1 table1 CROSS
JOIN table2 table2
WHERE table2.date=
    (SELECT max(table2i.date)
     FROM table3 table3 CROSS
     JOIN table2 table2i
     WHERE table2i.id=table2.id
       AND table2i.FC=?
       AND table2i.TC=?
       AND table2i.TCOB=?
       AND table2i.FCOB=?
       AND table2i.TC=table1.id
       AND table2i.RT=?
       AND table2i.RTOB=?
       AND table3.id=?
       AND table2i.date<=table3.CD)

What is the primary key of Table 2? – Tab Alleman Jul 07 '15 at 15:34 — Tab Alleman, Jul 07 '15 at 15:34
@TabAlleman The cloumn "id" is the primary key of Table2 – Rajeev Abatfb Jul 07 '15 at 17:06 — Rajeev Abatfb, Jul 07 '15 at 17:06

score 0 · Answer 1 · answered Jul 07 '15 at 17:11

To limit your join to a single row, instead of joining on the date, join on the id:

WHERE table2.id=(
  SELECT TOP 1 table2i.id
  FROM table2 table2i
  CROSS JOIN table3 
  WHERE ...
  ORDER BY date DESC
)

the ORDER BY date DESC forces it to choose a row that has the highest date value, but limits it to only one row (top 1).

Rajeev Abatfb · Answer 2 · 2015-09-07T03:47:08.587

Using below solved the issue :

SELECT table2.ER AS col_0_0_,
       table1.CP AS col_1_0_
FROM CV table1 CROSS
JOIN ER_VIEW table2
WHERE table2.FCI=?
  AND table2.TCI=?
  AND table2.TCOB=?
  AND table2.FCOB=?
  AND table2.TCI=table1.id
  AND table2.RTI=?
  AND table2.RTOB=?
  AND table2.date=
    (SELECT max(table2i.date)
     FROM DATE_VIEW table3 CROSS
     JOIN ER_VIEW table2i
     WHERE table2i.FCI=?
       AND table2i.TCI=?
       AND table2i.TCOB=?
       AND table2i.FCOB=?
       AND table2i.RTI=?
       AND table2i.RTOB=?
       AND table3.id=?
       AND table2i.date<=table3.CD)

SQL | Criteria Query for max value in a subquery

2 Answers2