tricky SQL?

[pando]

Hi

Need little help here, trying to write an SQL that do as follows

have two tables and when I join them I would get something like

Column A---Column B
New York----David
New York----John
New York----Alex
Atlanta-------Chris
Florida-------Phil
L.A-----------David
L.A-----------Jenny
L.A-----------Boris
L.A-----------Joseph
Miami--------Alex
Miami--------Will

Now I want the query to return just a limited rows using rownum, for example

where rownum <= 3

However this would return

Column A---Column B
New York----David
New York----John
New York----Alex

ONLY

But I need all results of first column <= 3, what i am saying is that I need the query to return me

Column A---Column B
New York----David
New York----John
New York----Alex
Atlanta-------Chris
Florida-------Phil

where column A has 3 values

is this possible at all? I have seek a few ways but cant do it!!!

Cheers

[pss]

Select Column_B from Table
where Column_A in (select distinct Column_A from Table where rownum<=3);

Sergey.

[jmodic]

Pando,

If you are using 8.1.6 or above you can take advantage of analytycal functins. In your case you would use new ROW_NUMBER() function:

SELECT column_a, column_b FROM your_table
WHERE (ROW_NUMBER() OVER (PARTITION BY column_a)) <= 3;

HTH,

[pando]

sorry I forgot to say that the result set is from a join between two tables


btw I am using 8.1.6.3 on Solaris 2.6

[jmodic]

I don't have any database handy to test my SQLs, but I just looked at SQL manual and found out that you can only use analytical functions in the SELECT LIST and in ORDER BY clause of a query, not in the WHERE clause. So you have to use inline views. For joining two tables and showing only up to 3 names for each city, you would use:

SELECT colA, colB FROM
(SELECT t1.colA, t2.colB, ROW_NUMBER() OVER (PARTITION BY column_a) row_num_for_colA
FROM t1, t2 WHERE .....)
WHERE row_num_for_colA <= 3;

[pando]

Is that partition compulsory...? The tables I am querying arent partitioned... I will have a look at documentation as well, did you find this in the SQL guide?

[jmodic]

In analytical functions, PARTITION BY have nothing to do with table partitions (or index partitions). It merely defines on what range the analytical function is aplied. In this case, a distinct city name reperesents a partition and on each of this partitions each rows gets sequential row number (starting with 1 at each new "partition"). And yes, these new analytical functions are explained in SQL manual (from 8.1.6 onwards).

[pando]

Hm tried this and it wouldnt work, I am getting a result set like this


Column A---Column B---ROW_NUM_FOR_COL_A
New York----David--------1
New York----John---------2
New York----Alex----------3
Atlanta-------Chris---------1
Florida-------Phil------------1
L.A-----------David---------1
L.A-----------Jenny---------2
L.A-----------Boris----------3
Miami--------Alex------------1
Miami--------Will--------------2

Basically it´s filtering on the last column, and I want to *ASSIGN* row number 1 to New York, 2 to Atlanta, 3 to Florida etc...

:(

[moff]

Hi,

I'm not sure if this would help in your quest for the answer but

If you create a temporary table of distinct values of columnA then use this in your select with the rownum <=3.

eg

create table temp_tab as (select distinct(columnA) from t1;

select t1.columnA, t2.columnB
from t1, t2
where t1.columnA in
(select columnA from temp_tab where rownum <=3)
and
......

In your example it will return the following

Atlanta ----Chris
Florida ----Phil
L.A -----Boris
L.A -----David
L.A -----Jenny
L.A -----Joseph

(It sorts it into order when you create the temp table using select distinct)

Hope this helps.

If not I'd be interested to see how the problem is solved. I'd appreciate it if you could E-Mail the solution to cmoffat99@yahoo.co.uk - It's got me a little bugged on how you can get the exact result you asked for - unsorted.

Kind Regards

Moff

[pando]

No that wouldnt work, the example I posted is a simplified version what we are trying to do in a search engine (that´s why I have to limit the result set using rownum) If I start to create temporary tables it would be one temporary table for each search since the conditions in the search can vary quite a lot...



[Edited by pando on 03-06-2001 at 07:08 AM]