Re: Distinct Rows?

In article <953565654.688.0.nnrp-04.9e9859ae_at_news.demon.co.uk>, "Kevin White" <kevin_white_at_lagan.com> wrote:
> Does anyone know how to;
>
> I have a table like so,
>
> customerNumber Status
> 10010 1
> 10011 1
> 10015 2
>
> I want to select the first customer number from the list with a
Status of 1.
>
> If I try this in a stored procedure it wants to return both customer
number
> 10010 and 10011.
>
> Thanks Kevin...
>

Kevin, you have two choices here. If you do not care which row (customer) is returned but just need it to have a status of one you can use the pseudo column rownum to limit your result set to one row.

select cust_no from table_x where status = '1' and rownum = 1;

You can find an explanation of rownum and its limits in the SQL manual. Rownum and sorts do not go along together well.

If you need the customer to be the first customer in alphabetic or customer number order then you can

In pl/sql you define your sql as a cursor with a order by clause open the curson
fetch the first row
close cursor
--
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 20 2000 - 09:51:57 CST