SQL query help

[nbrege]

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

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...

[Arnoutdv]

No quotes needed

[Zvoni]

And why in blazes is ID a String?

And which DBMS?

Untested

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

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

[almasadir.te]

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

[gigemboy]

Arnoutdv said it best.

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

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