Article: Another Recruiter Asks a SQL Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Another Recruiter Asks a SQL Question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-03-05 : 05:22:51

Sandeep writes Hi IT Guys, during an interview, a recruiter asked me "How can you retrieve the first n number of fields using sql statement withought using field names?" ex. Table A contains 50 fields and I want to select first 10 fields. I don't want to mention all the 10 fields name. I love these questions!

Article Link.

tribune
Posting Yak Master

105 Posts

Posted - 2001-12-27 : 14:13:30

DECLARE @TableName VarChar(50)
DECLARE @NumRows int

SELECT @NumRows = 3
SELECT @TableName = 'Computers'

SELECT COL_NAME(OBJECT_ID(@TableName),ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @TableName AND ORDINAL_POSITION <= @NumRows

JamesH
Posting Yak Master

149 Posts

Posted - 2002-01-10 : 13:04:43

select Top 10 b.name, b.colid from sysobjects a inner join syscolumns b
on a.id=b.id
where a.Name = [Table/View Name here]
order by b.colid

JamesH
Posting Yak Master

149 Posts

Posted - 2002-01-10 : 13:10:24

Forgot the:

And a.Type in ('U','V')

I know he only asked for the table, but the next stupid thing would be: Could you do the same thing for a view? Sorry, the sarcasm builds after 1:00.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-10 : 14:56:56

A reply might be
"Why do you want to do this".

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

JamesH
Posting Yak Master

149 Posts

Posted - 2002-01-11 : 09:06:17

True. I'm not even going to try and guess why anybody would want to do this as it would'nt return any viable results (information vs. data).

JamesH

sachin1sharma
Starting Member

1 Post

Posted - 2003-04-18 : 15:21:17

Its simple.
say if you want to select first 2 fields from n fields. The query would be like this.

SELECT top 2 column_name
from INFORMATION_SCHEMA.Columns
where table_name = 't2'
and ordinal_position <= 10

syamt
Starting Member

1 Post

Posted - 2003-11-19 : 10:55:02

Also you can try

select top 10 Name
from syscolumns where id =object_ID('tblOrder')
order by colid

(if you want this to work in SQL7)

Syam

pklotka
Starting Member

1 Post

Posted - 2006-09-19 : 12:37:23

If you don't need the column names for some reason...

select 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
from mytable

This will get you the first ten columns, however it will explode if there are less then ten columns available.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 13:22:37

quote:
Originally posted by pklotka

If you don't need the column names for some reason...

select 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
from mytable

This will get you the first ten columns, however it will explode if there are less then ten columns available.

That does not work in SQL Server. For example, this just returns columns of numbers


select 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 from sysobjects

CODO ERGO SUM

kjones123
Starting Member

1 Post

Posted - 2006-10-25 : 14:40:24

Using on of the examples above I came up with -

SELECT top 3 column_name
from INFORMATION_SCHEMA.Columns
where table_name = 'authors'

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-25 : 15:37:41

quote:
Originally posted by nr

A reply might be
"Why do you want to do this".

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Or, why have you embedded business rules into your column ordinals? What other standards do you have that violate the rules of logic and mathematics?

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources