SQL query help

I'm trying to delete a row in a table using a sub-query, like as below:

Code:

---

delete from MyTable where id = '(select id from MyTable order by ctime limit 1)'

---

id is a string value & ctime is a timestamp value. So basically the sub-query gets the id value from the row with the oldest timestamp & the first part of the query deletes the row having that id value. My problem is that I don't know how the handle the single quotes that are required around the id value. I can do this using 2 separate queries, but I'd like to combine them into 1 query. Thanks for any help...

No quotes needed

And why in blazes is ID a String?

And which DBMS?

Untested

Code:

---

WITH
  CT AS (SELECT ID, ROW_NUMBER() OVER(ORDER BY ctime) AS RN FROM MyTable)

DELETE FROM MyTable AS MT
INNER JOIN CT ON MT.ID=CT.ID AND CT.RN=1

---

Without CTE

Code:

---

DELETE FROM MyTable AS MT
INNER JOIN
  (SELECT ID, ROW_NUMBER() OVER(ORDER BY ctime) AS RN FROM MyTable) AS CT
ON MT.ID=CT.ID AND CT.RN=1

---

Hi bro i seen you was have issue in draw line and you got it resolve can you also help me i have same issue thank you

Arnoutdv said it best.

Here's a pretty version (defensive in case ctime is not distinct)

Code:

---

DELETE MT
FROM  dbo.MyTable AS MT
WHERE  MT.id =
(
    SELECT TOP (1) id
    FROM  dbo.MyTable
    ORDER BY
          ctime,
          id
)

---