how to reterive a single record from a table

Question

how to reterive a single record from a table

syed_jafri786

SSC Veteran

Points: 280
More actions
January 29, 2009 at 9:24 am

#125997

I was asked in interview to write T-SQL statement to retrieve a single entry at row #6 and column #2 from a table without looking

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you know how to write a Select statement?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

syed_jafri786 SSC Veteran Points: 280 More actions · Answer 2

Can u kindly post the t-sql statement instead of asking question. Mr. genius.

Lowell SSC Guru Points: 323518 More actions · Answer 3

the interview question was a test of your basic knowledge on how to query for data.

you failed.

If you can't answer it, it would show that you have no experience in even writing a query.

insulting people who are trying to help will not get you anything.

if they gave you the table name and column2's name,

I can think of at least two ways to get the 6th row of any table, one using row_number() function and the other using nested TOP() functions. , three ways if the "6th row" mean WHERE the table had an identity field, and they meant 6 row mean thatfield = 6

read up on those, and practice. One of the best things you can do is copy and paste code you see here in teh forums, and make them work so you understand them.

if they did not give you the column name,

to get the column 2, without knowing the name of the column, would require dynamic sql and then either of the two methods above.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 4

Lowell (1/29/2009)
... insulting people who are trying to help will not get you anything...

here here

Ok Mr Grumpy:

-- Get a sample of records from a system table for Mr G

SELECT TOP 10 * FROM sys.objects ORDER BY object_id

-- Return the second row

WITH SomeTable AS (

SELECT*,

ROW_NUMBER() OVER ( ORDER BY object_id ) AS RowNumber

FROMsys.objects )

SELECT*

FROMSomeTable

WHERERowNumber = 2

Please note that this is for sql 2005.

Max

tinku.reddy SSChasing Mays Points: 635 More actions · Answer 5

Hi Guys,

This is an Interesting post. Even I am fresher, I would like to know the solution.

If any one the query plz post here.

Thank you

No Body Is Perfect In This Imperfect World

GSquared SSC Guru Points: 260824 More actions · Answer 6

syed_jafri786 (1/29/2009)
Can u kindly post the t-sql statement instead of asking question. Mr. genius.

Until you became rude and sarcastic, yes, I could do that. Now, I seem to have lost the ability. Sorry.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

tinku.reddy SSChasing Mays Points: 635 More actions · Answer 7

Guys,

I got it. Max, Thanks a lot. Learned a lot about row_number() great.

Thanks

No Body Is Perfect In This Imperfect World

richardq.s SSC Enthusiast Points: 198 More actions · Answer 8

declare @tbname as varchar(128)

declare @column as varchar(128)

set @tbname='your table name'

select @column=name from sys.columns where object_name(object_id)=@tbname and column_id=2

exec ('select '+@column+' from (select top 6 row_number() over( order by getdate()) as row,'

+@column+' from '+@tbname+') t where row=6')

syed_jafri786 SSC Veteran Points: 280 More actions · Answer 9

Thanks a lot Max. The reason I got mad was that he ask me whether I know select statement or not. Ofcourse I know select statement but it is not necessary I can write any query. Any way sorry for what I wrote.

syed_jafri786 SSC Veteran Points: 280 More actions · Answer 10

Thanks a lot Max. The reason I got mad was that he ask me whether I know select statement or not. Ofcourse I know select statement. It is like if someone ask you to teach him parallel parking and instead of giving instruction you ask him whether he knows driving or not.

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 11

Np, you probably just hit G's funnny bone, hope the site helps you to grow your sql skills, it certainly has mine.

Max

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Max (1/29/2009)
Lowell (1/29/2009)
... insulting people who are trying to help will not get you anything...
here here

... but you still answered the question, didn't you? 😉 If it were directed at you, would you still have answered?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

The real answer to this question should have been "Since data is not guaranteed to be in any order in a table or query result, and ORDER BY would be necessary to determine what row # 6 would be... How do you want it sorted? Also, the order of columns in a database table should never be important because the order may change. What is the name of column #2?" 😉

THAT would be the answer I'd be looking for to separate the Men from the Boys...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

GSquared (1/29/2009)
Do you know how to write a Select statement?

Heh... bad day, Gus? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)