Query SQL with inner join

Query SQL with inner join

Hi,

I have a transaction table with heaps of rows. I want to create a SQL script that gets all transactions with the minimum transaction date. Eventually I want to purge/delete old rows using the delete API.

Logically this is two steps:
1. Get minimum trans date from transaction table.
2. Select all rows in the transaction table where trans date equals the minimum trans date from previous step.

I tried the following 2 SQL statements but receive different errors messages:
SELECT
     COUNT(*)
FROM
     "rpt_Transaction"
 WHERE
     DATE(TransStartDT) IN (SELECT DATE(MIN("TransStartDT")) as min_TransStartDT FROM "rpt_Transaction")

I received an error saying the query is taking too long to run and therefore was killed internally.

I decided to use a INNER JOIN to see if it works:
SELECT
     COUNT("T01"."TransactionID") 
FROM
     "rpt_Transaction" "T01"   INNER JOIN
          (SELECT DATE(MIN("TransStartDT")) as TransStartDT FROM "rpt_Transaction")  "T02"
    ON DATE("T01"."TransStartDT") = "T02"."TransStartDT"

I used tables aliases. The error message I get is " Only SELECT queries are allowed".

The top two queries work on a SQL Server and Oracle environment. May be not the best in terms of run time but they do work.

Any suggestions on how to build a valid SQL query for the above requirement?

Thanks
Sunil