Return only the Third Row

25 Aug 2005 6:09 PM

John Walker

Hi,

I have a table Customers with only one field Customer_name.

Here's my simple query:
    select customer_name
    from customers
    order by customer_name

Is there a way to modify this query to have it return only 3rd row?

I know how to get the 3rd row using a cursor, but was hoping that there
might be an easier way to do it.

Thanks in advance,
John

25 Aug 2005 6:16 PM

Chandra

hi john

just see if this can solve the purpose

select top 1 customer_name
from
(
select top 3 customer_name
from customers
order by customer_name
)derived
order by customer_name desc

please let me know if u have any questions

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---------------------------------------

Show quote

"John Walker" wrote:

> Hi,
>
> I have a table Customers with only one field Customer_name.
>
> Here's my simple query:
>     select customer_name
>     from customers
>     order by customer_name
>
> Is there a way to modify this query to have it return only 3rd row?
>
> I know how to get the 3rd row using a cursor, but was hoping that there
> might be an easier way to do it.
>
> Thanks in advance,
> John
>

25 Aug 2005 6:51 PM

John Walker

Chandra,
Thanks! I believe that logically your query will do what I need it to do,
but I run into a problem when I use a variable in the query. It turns out
that I can't hard code the 3 because it can be other values as well. When I
do this:

DECLARE @X AS INT
SELECT @X = 1

select top 1 customer_name
from
(
select top @X customer_name
from customers
order by customer_name
)derived
order by customer_name desc

I get:

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@X'.

I think your query will work, but do you know a way that I can be made to
work dynamically?

Show quote

"Chandra" wrote:

> hi john
>
> just see if this can solve the purpose
>
> select top 1 customer_name
> from
> (
> select top 3 customer_name
>     from customers
>     order by customer_name
> )derived
> order by customer_name desc
>
> please let me know if u have any questions
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---------------------------------------
>
>
>
> "John Walker" wrote:
>
> > Hi,
> >
> > I have a table Customers with only one field Customer_name.
> >
> > Here's my simple query:
> >     select customer_name
> >     from customers
> >     order by customer_name
> >
> > Is there a way to modify this query to have it return only 3rd row?
> >
> > I know how to get the 3rd row using a cursor, but was hoping that there
> > might be an easier way to do it.
> >
> > Thanks in advance,
> > John
> >

25 Aug 2005 7:02 PM

Chandra

in this case, u need a dynamic query to be built

declare @query nvarchar(1000)
declare @x int
set @x=3
set @query = N'select top 1 customer_name
from
(
select top ' + cast(@X as varchar) + ' customer_name
from customers
order by customer_name
)derived
order by customer_name desc'
exec sp_executesql @query

hope this answered the question

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---------------------------------------

Show quote

"John Walker" wrote:

> Chandra,
> Thanks! I believe that logically your query will do what I need it to do,
> but I run into a problem when I use a variable in the query. It turns out
> that I can't hard code the 3 because it can be other values as well. When I
> do this:
>
> DECLARE @X AS INT
> SELECT @X = 1
>
> select top 1 customer_name
> from
> (
> select top @X customer_name
>     from customers
>     order by customer_name
> )derived
> order by customer_name desc
>
> I get:
>
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@X'.
>
> I think your query will work, but do you know a way that I can be made to
> work dynamically?
>
> "Chandra" wrote:
>
> > hi john
> >
> > just see if this can solve the purpose
> >
> > select top 1 customer_name
> > from
> > (
> > select top 3 customer_name
> >     from customers
> >     order by customer_name
> > )derived
> > order by customer_name desc
> >
> > please let me know if u have any questions
> >
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://www.SQLResource.com/
> > ---------------------------------------
> >
> >
> >
> > "John Walker" wrote:
> >
> > > Hi,
> > >
> > > I have a table Customers with only one field Customer_name.
> > >
> > > Here's my simple query:
> > >     select customer_name
> > >     from customers
> > >     order by customer_name
> > >
> > > Is there a way to modify this query to have it return only 3rd row?
> > >
> > > I know how to get the 3rd row using a cursor, but was hoping that there
> > > might be an easier way to do it.
> > >
> > > Thanks in advance,
> > > John
> > >

25 Aug 2005 7:37 PM

Anith Sen

You can use a correlated subquery or a self join to do this. Here are a
couple of examples with the Customers table in the Northwind database:

SELECT c1.ContactName
FROM Customers c1
WHERE ( SELECT COUNT( * )
           FROM Customers c2
          WHERE c2.ContactName <= c1.ContactName ) = @x ;

-- or

SELECT ContactName
FROM ( SELECT c1.ContactName, COUNT(*)
           FROM Customers c1, Customers c2
          WHERE c2.ContactName <= c1.ContactName
          GROUP BY c1.ContactName ) D ( ContactName, rank )
WHERE rank = @x ;

-- or

SELECT c1.ContactName
FROM Customers c1, Customers c2
GROUP BY c1.ContactName
HAVING SUM( CASE WHEN c2.ContactName <= c1.ContactName
                 THEN 1 ELSE 0 END ) = @x ;

If there are multiple values which match the same rank ( ties ), just adjust
the comparisons.

--
Anith

25 Aug 2005 6:18 PM

Alejandro Mesa

What is the criteria used to identitfy a row as the third in the table?.
There is not a specific order for rows in a table unless you use a unique
clustered index.

AMB

Show quote

"John Walker" wrote:

> Hi,
>
> I have a table Customers with only one field Customer_name.
>
> Here's my simple query:
>     select customer_name
>     from customers
>     order by customer_name
>
> Is there a way to modify this query to have it return only 3rd row?
>
> I know how to get the 3rd row using a cursor, but was hoping that there
> might be an easier way to do it.
>
> Thanks in advance,
> John
>

25 Aug 2005 6:58 PM

John Walker

Alejandro,

Actually I found out it needs to be dynamic, so I'm actually looking for row
X (not 3).

But in answer to your question, I am ordering it by customer_name so row X
will be the Xth customer alphabetically.

Thanks,
John

Show quote

"Alejandro Mesa" wrote:

> What is the criteria used to identitfy a row as the third in the table?.
> There is not a specific order for rows in a table unless you use a unique
> clustered index.
>
>
> AMB
>
> "John Walker" wrote:
>
> > Hi,
> >
> > I have a table Customers with only one field Customer_name.
> >
> > Here's my simple query:
> >     select customer_name
> >     from customers
> >     order by customer_name
> >
> > Is there a way to modify this query to have it return only 3rd row?
> >
> > I know how to get the 3rd row using a cursor, but was hoping that there
> > might be an easier way to do it.
> >
> > Thanks in advance,
> > John
> >

25 Aug 2005 7:23 PM

Alejandro Mesa

Are there ties?

declare @x int

set @x = 3

select *
from t1 as a
where
   (
   select count(*)
   from t1 as b
   where b.customer_name <= a.customer_name
   ) = @x
go

AMB

Show quote

"John Walker" wrote:

> Alejandro,
>
> Actually I found out it needs to be dynamic, so I'm actually looking for row
> X (not 3).
>
> But in answer to your question, I am ordering it by customer_name so row X
> will be the Xth customer alphabetically.
>
> Thanks,
> John
>
> "Alejandro Mesa" wrote:
>
> > What is the criteria used to identitfy a row as the third in the table?.
> > There is not a specific order for rows in a table unless you use a unique
> > clustered index.
> >
> >
> > AMB
> >
> > "John Walker" wrote:
> >
> > > Hi,
> > >
> > > I have a table Customers with only one field Customer_name.
> > >
> > > Here's my simple query:
> > >     select customer_name
> > >     from customers
> > >     order by customer_name
> > >
> > > Is there a way to modify this query to have it return only 3rd row?
> > >
> > > I know how to get the 3rd row using a cursor, but was hoping that there
> > > might be an easier way to do it.
> > >
> > > Thanks in advance,
> > > John
> > >

25 Aug 2005 7:13 PM

Damien

Ranking problem? Try:

CREATE TABLE #customers ( customer_name VARCHAR( 50 ) PRIMARY KEY )

INSERT INTO #customers
SELECT 'Arvin'        UNION
SELECT 'Bob'        UNION
SELECT 'Jean'        UNION
SELECT 'Gene'        UNION
SELECT 'Joe'        UNION
SELECT 'Phyllis'    UNION
SELECT 'Zebedee'

SELECT *
FROM
    (
    SELECT c1.customer_name, ( SELECT COUNT(*) FROM #customers c2 WHERE
c1.customer_name > c2.customer_name ) AS rank
    FROM #customers c1
    ) customer_rank
WHERE rank % 3 = 0

DROP TABLE #customers

Let me know how you get on.

Damien
Show quote

"John Walker" wrote:

> Hi,
>
> I have a table Customers with only one field Customer_name.
>
> Here's my simple query:
>     select customer_name
>     from customers
>     order by customer_name
>
> Is there a way to modify this query to have it return only 3rd row?
>
> I know how to get the 3rd row using a cursor, but was hoping that there
> might be an easier way to do it.
>
> Thanks in advance,
> John
>

25 Aug 2005 8:21 PM

John Walker

Damien,

Brilliant. Worked beautifully. Thanks your, and everyone's, help with this!

John

Show quote

"Damien" wrote:

> Ranking problem? Try:
>
> CREATE TABLE #customers ( customer_name VARCHAR( 50 ) PRIMARY KEY )
>
> INSERT INTO #customers
> SELECT 'Arvin'        UNION
> SELECT 'Bob'        UNION
> SELECT 'Jean'        UNION
> SELECT 'Gene'        UNION
> SELECT 'Joe'        UNION
> SELECT 'Phyllis'    UNION
> SELECT 'Zebedee'
>
>
> SELECT *
> FROM
>     (
>     SELECT c1.customer_name, ( SELECT COUNT(*) FROM #customers c2 WHERE
> c1.customer_name > c2.customer_name ) AS rank
>     FROM #customers c1
>     ) customer_rank
> WHERE rank % 3 = 0
>
>
> DROP TABLE #customers
>
>
> Let me know how you get on.
>
>
> Damien
> "John Walker" wrote:
>
> > Hi,
> >
> > I have a table Customers with only one field Customer_name.
> >
> > Here's my simple query:
> >     select customer_name
> >     from customers
> >     order by customer_name
> >
> > Is there a way to modify this query to have it return only 3rd row?
> >
> > I know how to get the 3rd row using a cursor, but was hoping that there
> > might be an easier way to do it.
> >
> > Thanks in advance,
> > John
> >