Interesting interview question...........

Hi all,
One small question.
I am having a table named maverick and having 2 fields, tkno(int) and
name(varchar(20)).
The table is filled with 10 entries.
tkno name
1 Zamsheer
2 Thushara
3 Kori
4 Attu
................etc
I want to retrieve the 6th record(any specified) from the table
........that's it.......
But the interesting factor is
U should not mention any field name in the query
don't use a cursor......
no stored procedure, no function... nothing
One single query should give the result......
Regards,
JaisonThe sixth row according to what order? tkno?
select top 1 *
from (
select top 6 *
from <table>
order by tkno asc
) TopSix
order by TopSix.tkno desc
Close enough?
ML
http://milambda.blogspot.com/|||If you really 'cant' use column names, you could use ordinal number of the
column. So, instead of order by tkno you can use order by 1
Offcourse, dont do that in a 'normal' environment.
MC
"ML" <ML@.discussions.microsoft.com> wrote in message
news:622ED66D-6581-4744-9D3B-C9C53BED400B@.microsoft.com...
> The sixth row according to what order? tkno?
> select top 1 *
> from (
> select top 6 *
> from <table>
> order by tkno asc
> ) TopSix
> order by TopSix.tkno desc
> Close enough?
>
> ML
> --
> http://milambda.blogspot.com/|||USe pubs
--2000
DECLARE @.param int
SET @.param = 3
Select j.* FROM Jobs J
WHERE @.param>=(SELECT Count(*) FROM Jobs B
WHERE B.Job_Id <= J.Job_Id )
ORDER By Job_Id
--2005
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ContactID DESC) As Rno, * FROM
Person.Contact) T
WHERE T.Rno = 5
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:FA6190B9-9A9C-4308-BD9E-91B91F52FD08@.microsoft.com...
> Hi all,
> One small question.
> I am having a table named maverick and having 2 fields, tkno(int) and
> name(varchar(20)).
> The table is filled with 10 entries.
> tkno name
> 1 Zamsheer
> 2 Thushara
> 3 Kori
> 4 Attu
> ................etc
> I want to retrieve the 6th record(any specified) from the table
> ........that's it.......
>
> But the interesting factor is
> U should not mention any field name in the query
> don't use a cursor......
> no stored procedure, no function... nothing
> One single query should give the result......
> Regards,
> Jaison
>|||Thanks...........
"ML" wrote:

> The sixth row according to what order? tkno?
> select top 1 *
> from (
> select top 6 *
> from <table>
> order by tkno asc
> ) TopSix
> order by TopSix.tkno desc
> Close enough?
>
> ML
> --
> http://milambda.blogspot.com/|||The ordinal position, yes. That would fully comply with the requirement. And
I agree - to vulnerable to be of any real use.
ML
http://milambda.blogspot.com/