# Max Date Value?

**URL:** <https://forums.toadworld.com/t/max-date-value/59371>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [May 8, 2024, 5:43pm UTC](https://forums.toadworld.com/t/max-date-value/59371 "2024-05-08T17:43:51Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![srh5428](https://avatars.discourse-cdn.com/v4/letter/s/278dde/32.png) [@srh5428](https://forums.toadworld.com/u/srh5428)\
**Post date:** [May 8, 2024, 5:43pm UTC](https://forums.toadworld.com/t/max-date-value/59371/1 "2024-05-08T17:43:51Z")

</div>

Can anyone see where my subquery is wrong?

I am trying to find the latest date for the REVIEW\_DATE column, but my data is still showing all  
dates. Can anyone see what I am doing wrong?

Thank you

 ![image](https://global.discourse-cdn.com/quest/original/3X/8/f/8f0d745887869a4f61e8084a233655f9fbc75b95.png)

---

<div class="post-metadata">

**Author:** ![Gary.Jerep](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gary.jerep/32/205_2.png) [@Gary.Jerep](https://forums.toadworld.com/u/Gary.Jerep)\
**Post date:** [May 8, 2024, 7:59pm UTC](https://forums.toadworld.com/t/max-date-value/59371/2 "2024-05-08T19:59:22Z")

</div>

Don't see anything off-hand, but would be good to see the entire query.  
That said, the idea should work.

However, see links like the following for other options, like using CTEs, joining on the derived table, etc. (there are many more such links, of course...)

> **[SQL MAX() on date value - w3resource](https://www.w3resource.com/sql/aggregate-functions/max-date.php)**
>
> SQL MAX() on date value: Here we have discussed the usage of SQL MAX() function on date type of column of a table. SQL WHERE clause along with the SQL MAX() can be used as a subquery to find the maximum value of a column based upon some condition.

> <https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date>

---

<div class="post-metadata">

**Author:** ![srh5428](https://avatars.discourse-cdn.com/v4/letter/s/278dde/32.png) [@srh5428](https://forums.toadworld.com/u/srh5428)\
**Post date:** [May 9, 2024, 11:10am UTC](https://forums.toadworld.com/t/max-date-value/59371/3 "2024-05-09T11:10:19Z")

</div>

Hi Gary,

Please see the query below:

SELECT InOp.IOR\_NO AS "IOR #",  
InOp.OP\_COMP AS "OpCo",  
--Operating Region  
InOp.CREW\_AREA AS "District",  
--Work Group  
--Work Group Adjusted  
InOp.STATION AS "Sub",

```
   InOp.DEVICE AS "Equipment/Line Name",
   InOp.VOLTAGE_LEVEL || 'kV' AS "Voltage",
   InOp.POLE_PAD AS "Pole/Pad #",
   
   InOp.PROBLEM_DETAIL AS "Problem Description",
   InOp.ACTION_COMMENT AS "Repair Status",
   InOp.OUTAGE_TYPE AS "Outage Type",
   InOp.OUTAGE_STATUS AS "Outage Status",
   InOp.APP_NUM AS "Switching Order #",
   InOp.WORK_GROUP_AREA AS "Work Group Area",
   R.REVIEWED_BY AS "Person Responsible",
   to_char(R.REVIEW_DATE, 'mm/dd/yyyy') AS "Last Reviewed Date",
  
   InOp.LOCATION_DETAIL AS "Location",
   Trim(InOp.POWERON) AS "Event #",
   Trim(InOp.SAP) AS "SAP Order #",
   Trim(InOp.WR_NUM) AS "WR #",
   InOp.CREATED_BY AS "Created By",
   InOp.CREATE_DATE AS "Created Date"

```

FROM TOA\_APP.TOA\_INOP InOp  
LEFT JOIN TOA\_APP.TOA\_INOP\_REVIEW R ON R.INOP\_ID = InOp.INOP\_ID

WHERE REVIEW\_DATE =  
(SELECT max(R.REVIEW\_DATE)  
FROM TOA\_APP.TOA\_INOP\_REVIEW  
WHERE R.REVIEW\_DATE IS NOT NULL )

```
    AND InOp.IOR_NO = 'IOR-19-01466-PN'

```

ORDER BY InOp.IOR\_NO DESC

---

<div class="post-metadata">

**Author:** ![Gary.Jerep](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gary.jerep/32/205_2.png) [@Gary.Jerep](https://forums.toadworld.com/u/Gary.Jerep)\
**Post date:** [May 9, 2024, 6:29pm UTC](https://forums.toadworld.com/t/max-date-value/59371/4 "2024-05-09T18:29:22Z")

</div>

I've been doing some quick testing on a condensed version of your query, including its structural components (embedded SELECT in WHERE, ORDER BY, etc.) and I get proper results, so cant' reproduce the non-max dates you're seeing.

Only thing I see at this point is that you're not qualifying your REVIEW\_DATE within the WHERE clause... e.g.  
...  
 ![image](https://global.discourse-cdn.com/quest/original/3X/0/d/0d3602c5a104f6f94b7bba3e3bcb98d6105d752f.png)  
...

Any SQL experts out there? Again, there are other ways of accomplishing this, but Seth's method to grab the max date should work.

---

<div class="post-metadata">

**Author:** ![srh5428](https://avatars.discourse-cdn.com/v4/letter/s/278dde/32.png) [@srh5428](https://forums.toadworld.com/u/srh5428)\
**Post date:** [May 9, 2024, 7:14pm UTC](https://forums.toadworld.com/t/max-date-value/59371/5 "2024-05-09T19:14:12Z")

</div>

Gary, can you think of another way to do this? I looked at those URLs you sent, and I don't really understand them to be honest. I am a novice when it comes to SQL

---

<div class="post-metadata">

**Author:** ![srh5428](https://avatars.discourse-cdn.com/v4/letter/s/278dde/32.png) [@srh5428](https://forums.toadworld.com/u/srh5428)\
**Post date:** [May 10, 2024, 2:14pm UTC](https://forums.toadworld.com/t/max-date-value/59371/6 "2024-05-10T14:14:19Z")

</div>

Gary - I ended doing a third level subquery and that seemed to work.

 ![image](https://global.discourse-cdn.com/quest/original/3X/a/1/a1dd6fae3063c20bf5c489559e731bf6aa00675c.png)