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

30 Jun 2006 11:19 AM

Jaison Jose

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

30 Jun 2006 11:30 AM

ML

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/

30 Jun 2006 11:31 AM

MC

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

Show quote

"ML" <M*@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/

30 Jun 2006 11:50 AM

ML

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/

30 Jun 2006 11:42 AM

Jaison Jose

Thanks..............

Show quote

"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/

30 Jun 2006 11:41 AM

Roji. P. Thomas

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

Show quote

"Jaison Jose" <JaisonJ***@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
>