SQL Statement Help [message #19380] |
Wed, 13 March 2002 11:32 |
Sam
Messages: 255 Registered: April 2000
|
Senior Member |
|
|
I have a table X with col CITY,ADDRESS.
CITY is PK and each CITY has many addresses.
I want to select say Fifty Addresses from
this table with the following criteria :
CITY 1 TEN ADDRESSES
CITY 2 TEN ADDRESSES
CITY 3 TWENTY ADDRESS
CITY 4 TEN ADDRESSES
I am not particular about the order in which
the address is to be picked it can be in any order.
Thanks in Advance.
|
|
|
Re: SQL Statement Help [message #19381 is a reply to message #19380] |
Wed, 13 March 2002 11:48 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
select city, address
from x
where city = 'city 1'
and rownum <= 10
union all
select city, address
from x
where city = 'city 2'
and rownum <= 10
union all
select city, address
from x
where city = 'city 3'
and rownum <= 20
union all
select city, address
from x
where city = 'city 4'
and rownum <= 10
order by 1, 2;
|
|
|
Re: SQL Statement Help [message #19422 is a reply to message #19380] |
Fri, 15 March 2002 06:38 |
Sri DHAR
Messages: 22 Registered: March 2002
|
Junior Member |
|
|
Todd,
I liked this approach, I 'm just interested in knowing
if there is a way to do it with out hard coding the city values like 'city 1' or 'city 2'.
Thanks in advance,
|
|
|
Re: SQL Statement Help [message #19424 is a reply to message #19422] |
Fri, 15 March 2002 07:55 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
As a static SQL statement, not really, because the requirement here was to pair each city with a specific number of rows.
You could, though, build and execute this statement dynamically (say you had a table listing desired cities and address counts).
|
|
|