SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Another Recruiter Asks a SQL Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/05/2001 :  05:22:51  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 03/22/2001 :  14:57:49  Show Profile  Reply with Quote
Top 10 answers

Is it me or is this that simple?
Using Northwind DB and Orders table
...SELECT TOP 10 * FROM orders

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 03/23/2001 :  10:20:42  Show Profile  Visit graz's Homepage  Reply with Quote
First 10 columns

A better way to write the question might have been first 10 columns, not first 10 rows.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 03/28/2001 :  14:30:26  Show Profile  Reply with Quote
Answer to Sandeep on the first ten columns

Sandeep,

apply this

declare @col1 varchar(50), @col2 varchar(50)
select @col1 = COL_NAME(OBJECT_ID('authors'), 1) ,
@col2 = COL_NAME(OBJECT_ID('authors'), 2)
exec ('Select ' + @col1 + ',' + @col2 + ' from authors' )

I'm not sure what this recruiter or you meant by three line.
Do you meant by three line with no comma or three line of differrent command line.

Anyway. post the question on swynk.com. I'm there every day to answer questions.

have fun

Go to Top of Page

tribune
Posting Yak Master

USA
105 Posts

Posted - 12/27/2001 :  14:13:30  Show Profile  Reply with Quote
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

Go to Top of Page

JamesH
Posting Yak Master

USA
149 Posts

Posted - 01/10/2002 :  13:04:43  Show Profile  Reply with Quote

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

Go to Top of Page

JamesH
Posting Yak Master

USA
149 Posts

Posted - 01/10/2002 :  13:10:24  Show Profile  Reply with Quote
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.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/10/2002 :  14:56:56  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

JamesH
Posting Yak Master

USA
149 Posts

Posted - 01/11/2002 :  09:06:17  Show Profile  Reply with Quote
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

Go to Top of Page

sachin1sharma
Starting Member

India
1 Posts

Posted - 04/18/2003 :  15:21:17  Show Profile  Reply with Quote
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


Go to Top of Page

syamt
Starting Member

USA
1 Posts

Posted - 11/19/2003 :  10:55:02  Show Profile  Send syamt a Yahoo! Message  Reply with Quote
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
Go to Top of Page

pklotka
Starting Member

USA
1 Posts

Posted - 09/19/2006 :  12:37:23  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/19/2006 :  13:22:37  Show Profile  Reply with Quote
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
Go to Top of Page

kjones123
Starting Member

1 Posts

Posted - 10/25/2006 :  14:40:24  Show Profile  Reply with Quote
Using on of the examples above I came up with -

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

Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/25/2006 :  15:37:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000