SQL Server Performance Forum – Threads Archive

Query to Get single row from a table

[blueRecently in an Interview I was asked to write a Query with follwing Criteria][/blue] 1. There is a Table with name X which contains soem valid data. 2. We don’t know the column name in the Table. The result set of the Query should return only one row. I tried with Top key word. but there is no use it’s asking for colunm Name. I’m very much eager to know the Answer [<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />][<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />] Thanks in advance for taking time for replying to my post.

May I say, that is one of the strangest questions I’ve ever seen?
SELECT TOP 1 *
FROM X would be one approach. And not the best, btw. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

SELECT name FROM syscolumns WHERE [id] = OBJECT_ID(‘<tablename>’) is this what you mean?

…or
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘X’
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

another way of producing the same result..cool <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I think Frank’s first reply is the answer [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

This might also be worth a try – if your table has an identity column, you can use the identitycol keyword: SELECT TOP 1 identitycol FROM MyTable

…and if your table has a UNIQUEIDENTIFIER column having the ROWGUIDCOL property defined, you can use
SELECT TOP 1 ROWGUIDCOL
FROM x
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

]]>