Joining a table on itself

Question

Is there a better way to write this SQL query?

SELECT *,  (SELECT TOP 1 columnB FROM mytable WHERE mytable.columnC = T1.columnC ORDER BY columnD) as firstRecordOfColumnB
FROM
    (SELECT * FROM mytable WHERE columnA = 'apple') as T1

Notice that columnC is not the primary key.

Answer 1

This might be better in case of performance:

SELECT
  *,
  (TOP 1 myLookupTable.columnB FROM mytable AS myLookupTable WHERE myLookupTable.keyColumn = mytable.keyColumn) as firstRecordOfColumnB
FROM
  mytable
WHERE
  columnA = 'apple'

But for the TOP 1 part I don't know any better solution.

Edit: If the keyColumn is unique, the data in firstRecordOfColumnB would be the same as in mytable.columnB. If it's not unique at least you need to sort that data to get a relevant TOP 1, example:

SELECT
  *,
  (TOP 1 myLookupTable.columnB FROM mytable AS myLookupTable WHERE myLookupTable.keyColumn = mytable.keyColumn
   ORDER BY myLookupTable.sortColumn) as firstRecordOfColumnB
FROM
  mytable
WHERE
  columnA = 'apple'

Answer 2

If the keyColumns is really a key column (i.e. unique), than the query can definitly be written more elegantly and efficiently...

SELECT
  *, columnB
FROM
  mytable
WHERE
  columnA = 'apple'