tricky SQL?

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

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

Sergey.

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,

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

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;

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?

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).

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...

:(

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 [email protected] - It's got me a little bugged on how you can get the exact result you asked for - unsorted.

Kind Regards

Moff

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]

I'll suppose you are doing a join between a table having location (table LOC, column LOCATION) with another having person names (table PERSON, column NAME). You must have a column linking these tables (let's call DEPT_ID). Try the query:

select A.LOCATION, B.NAME
from LOC A, PERSON B
where A.DEPT_ID=B.DEPT_ID
and B.DEPT_ID in
(select distinct DEPT_ID from LOC where rownum<=3)
;



Adriano.

Thank you for the suggestion I will have to have a look since actually i am joining 8 tables :D So I dont know if this would be feasible

The only difference is that you'll have to use 7 joins conditions instead of one. Despite ORDER BY, GROUP BY etc, your query won't be degradated by joining tables.


Adriano.

Well the query is based on about 10 search conditions that's why I am not sure this would be feasible... anyway will try this later

Your problem is that you want to select the first three DISTINCT values from a table, not the first three rows. In order to do that, the query would be:

select colA from (select distinct colA from tableA) where rownum<=3

So first you have a resultset with all distinct values ant only after you apply the rownum to this resultset.
These stated, you can join this resultset with anything you want:

select t1.colA, t2.colB from (select colA from (select distinct colA from tableA) where rownum<=3) t1, table2 t2 where t1.colA=t2.colC //as an example

I tried this type of query in one of my databases and the result was correct. The query was something like:
select t.vehicle_id_no, v.vehicle_id from (select vehicle_id from (select distinct vehicle_id from vehicle_history) where rownum<=3) v, vehicles t where t.vehicle_id=v.vehicle_id
Hope it helps you,
Mara

Well I guess corrrelated subquery would work, still have to try it was too busy today ....

Actually, jmodic was on the right track, as the analytical functions are your best bet, but before I get to the solution...

There were many excellent answers here. The problem you are going to have is implementing them, since your original statement sounds rather complicated. Given this complication, however, one must also consider performance. (Personally, I think one should *always* consider performance, but that's just me :) ). With that in mind, restricting the result set at the earliest possible time would be best. Therefore, the suggestion of finding the disting values first, and restricting that set might be best. However, this can get extremely complicated if you do not know your ultimate result set. I mean, just because I pick 3 states that I know are in table B does not mean that those 3 states will appear in the final result set. Therefore, you would have to apply all the restrictive criteria just to get the distinct set of usable states, then re-join and re-restrict everything again to get the full results. So, while the performance-dictator in me screams that we should do everything humanly possible to restrict the result set as early as possible, I must admit that this may not be a case where that can be done. Mind you, I don't have all the details, so it *may* be possible and *should* be explored.

However, having said all that, what you appear to need is almost what jmodic gave you, just a different analytical function:

SELECT
---COLA,
---COLB
FROM
(
---SELECT
------DENSE_RANK() OVER (ORDER BY T1.COLA)
---------AS MYRANK,
------T1.COLA,
------T2.COLB
---FROM
------T1,
------T2
---WHERE
------...
---)
WHERE
---MYRANK <= 3


Hope this helps,

- Chris

BTW - call me slow, but am I the last one to know that Oracle now supports the CASE construct?? I would have thought that one would have been on the front page of the POST! :) I am beside myself with joy - I'm never going to have to write a DECODE again!!

Chris,

You ar ecorrect again with your answer. When I suggeted the use of ROW_NUM() function I didn't correctly understand what pando wants - I thought he wants to select persons for every city, but at the same time to limit the number of persons per city to 3. Now you've comme with the right function for his actual bussines rule.

As regarding the CASE operator (or is it a function?):
I would say FINALY! AFAIK other databases supported CASE for quite a long time before Oracle included it in 8.1.6. I said FINALY, but at the same time something in me says: "Argh, I have developed quite a few tricks and techniques in using DECODE, now I will not be able to show around with those tricks anymore;) . Seriously, it is funny that some of techniques with DECODE were not published widely (or not published at all), for example how to avoid nesting decodes in complicated IF-ELSIF logic, how to implement range operators (GREATER THEN, ....) in decode and so on. I've had a presentation at our local usergroup conference devoted entirely to DECODE.

But seriously, the introduction of CASE in oracle is true relief! When I had to read my own decode construct few weeks after I've wrote them, I had to study their logic again from the beginning. The logic of the complicated DECODEs is usually so hard to "decode", while now you look at CASE once and its logic is immediatelly selfunderstandable.

BTW, the railroad diagram of CASE in 8.1.6 documentation has bug - there should be no comma (,) between subsequent WHEN comparisons.

Quote:

---

Originally posted by chrisrlong
Actually, jmodic was on the right track, as the analytical functions are your best bet, but before I get to the solution...

However, this can get extremely complicated if you do not know your ultimate result set. I mean, just because I pick 3 states that I know are in table B does not mean that those 3 states will appear in the final result set. Therefore, you would have to apply all the restrictive criteria just to get the distinct set of usable states, then re-join and re-restrict everything again to get the full results.

---

I did run into this problem of restricting twice with correlated query, I am trying with this analytic function will let you know the results
And perfomance is very important in this query because it´s a search engine query so basically it will be called again and again with different crititerias

Cheers

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

Back again but with problems, I am not sure if I partitioned the wrong columns.
The query is

SELECT * FROM
(SELECT
IC_PROMOCIONES.NOMBRE,
IC_MODELOS.COD_RED,
IC_MODELOS.COD_AGE,
IC_MODELOS.COD_PRM,
IC_MODELOS.COD_MOD,
DENSE_RANK() OVER (PARTITION BY
IC_PROMOCIONES.COD_RED,
IC_PROMOCIONES.COD_AGE,
IC_PROMOCIONES.COD_PRM
ORDER BY
IC_PROMOCIONES.COD_RED,
IC_PROMOCIONES.COD_AGE,
IC_PROMOCIONES.COD_PRM) MYRANK
FROM
IC_MODELOS,
IC_PROMOCIONES,
IC_EMPRESA,
IC_POBLACION,
IC_TIPINMUEB,
IC_SBTINMUEB,
IC_MONEDA
WHERE
IC_PROMOCIONES.COD_PAIS = 2 AND
IC_PROMOCIONES.COD_COM = 15 AND
IC_PROMOCIONES.COD_PRV = 28 AND
IC_MODELOS.COD_TIP_INMU = 8 AND
IC_MODELOS.COD_MON = 1 AND
IC_PROMOCIONES.COD_RED = IC_MODELOS.COD_RED AND
IC_PROMOCIONES.COD_AGE = IC_MODELOS.COD_AGE AND
IC_PROMOCIONES.COD_PRM = IC_MODELOS.COD_PRM AND
IC_MONEDA.COD_MON = IC_MODELOS.COD_MON AND
IC_POBLACION.COD_POB = IC_PROMOCIONES.COD_POB AND
IC_TIPINMUEB.COD_TIP_INM = IC_MODELOS.COD_TIP_INMU AND
IC_SBTINMUEB.COD_SBT_INM = IC_MODELOS.COD_SBT_INMU AND
IC_EMPRESA.COD_AGE = IC_PROMOCIONES.COD_AGE
ORDER BY
IC_POBLACION.DES_POB,
IC_EMPRESA.RZN_SCL,
IC_PROMOCIONES.NOMBRE,
IC_MODELOS.PRECIO_PUBLICACION
)
WHERE MYRANK <= 3


The partition:
PARTITION BY
IC_PROMOCIONES.COD_RED,
IC_PROMOCIONES.COD_AGE,
IC_PROMOCIONES.COD_PRM

These 3 columns forms the primary key and are the one that I want to obtain the distinct values. The results I got are dodgy:

----COD_RED-----COD_AGE-----COD_PRM-----MYRANK

2----------596-------3331----------1
2----------858-------2--------------1
2----------858-------2--------------1
2----------849-------1--------------1
2----------849-------1--------------1
2----------600-------2345----------1
2----------600-------2345----------1
2----------1363------7777----------1

Instead of (I think)

MY RANK

8
7
6
5
4
3
2
1

Well actually it should be 1,2,3,4,5,6,7,8 ;)

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

Hi Pando,
I'm not very sure about the performance, but believe this is a

solution.

SELECT A.NAME, B.CITY, B.RNO FROM TABLE1 A, (SELECT CITY, ROWNUM RNO

FROM (SELECT DISTINCT CITY FROM TABLE2) B
WHERE A.XXX = B.XXX AND B.RNO <= 3

This will give you the serial as you wish in the output as well will
restrict the output to the value supplied to the RNO column.

- Nanda.

Well it probably works but I will have to check for search criteria twice which would be a huge perfomance impact
It would be my last choice if I cant get it working :0

I have taken two tables for this ,
i hope this will solve ur req...

other wise pl reply me

table citymaster ----- table personmaster
cno cno
c_name personname

sql:
select cityname,p_name as personname from personmaster A,
(select distinct cityno as cno, c_name as cityname from citymaster where rownum<4) master where
A.cno=master.cno

........
reply me
komethagan.s

Pando, please look at my solution again. The way you want to use the DENSE_RANK function is how I showed you - with a simple ORDER BY, not a PARTITION BY. You only want one set of rankings, not multiple. Further, exactly which field(s) do you want to break on? Your first example had 2 fields and you wanted to break on the first with the second as simple detail. Your current SQL has 3 fields. Do you want to break on the combination of all of them with the rest as detail, or do you want to break on the first 1 or 2 fields with the remaining 2 or 1 fields as detail?

Let's assume you want all 3 columns. The next issue is that of ordering. The only way to get this counting is with the OVER(ORDER BY ...). Now, this will order the result set and then start counting distinct (multi-column) values. HOWEVER, this ORDER BY is completely different than the query's ORDER BY, which may lead to unexpected results.

Going back to you original example. if the query returned:

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

Then using the DENSE_RANK() OVER (ORDER BY ColumnA) would *internally* do this:
Column A---MYRANK
Atlanta-------1
Florida-------2
L.A-----------3
L.A-----------3
L.A-----------3
L.A-----------3
Miami--------4
Miami--------4
New York----5
New York----5
New York----5

Which we are then restricting to MYRANK <=3. Note that the query's ORDER BY is on some other field (matching the original results up top). Therefore, the actual result set we would get back is:

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

However, note that paging down is easy and will get you the next set of results:

WHERE MYRANK >3 and MYRANK <=6:

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


As for nansbrain and komes - I hate to be a stickler on this point, but you really have to take the effort to read the existing 2 pages of replies before adding to the heap - both of those solutions were already suggested. Mind you, the other solutions might not have had the *exact* same code, but the idea of a distinct list restricted by rownum as a general solution was already discussed at length. If you think your post is truly a unique take on the situation, please take the time to say why.

Lecture over :).

Hope this helps,

- Chris

I have tried the distinct this morning but I was getting dodgy results

when I issued

SELECT
DISTINCT
IC_MODELOS.COD_RED,
IC_MODELOS.COD_AGE,
IC_MODELOS.COD_PRM
FROM
IC_MODELOS,
IC_PROMOCIONES,
IC_EMPRESA,
IC_POBLACION,
IC_TIPINMUEB,
IC_SBTINMUEB,
IC_MONEDA
WHERE
IC_PROMOCIONES.COD_PAIS = 2 AND
IC_PROMOCIONES.COD_COM = 15 AND
IC_PROMOCIONES.COD_PRV = 28 AND
IC_MODELOS.COD_TIP_INMU = 8 AND
IC_MODELOS.COD_MON = 1 AND
IC_PROMOCIONES.COD_RED = IC_MODELOS.COD_RED AND
IC_PROMOCIONES.COD_AGE = IC_MODELOS.COD_AGE AND
IC_PROMOCIONES.COD_PRM = IC_MODELOS.COD_PRM AND
IC_MONEDA.COD_MON = IC_MODELOS.COD_MON AND
IC_POBLACION.COD_POB = IC_PROMOCIONES.COD_POB AND
IC_TIPINMUEB.COD_TIP_INM = IC_MODELOS.COD_TIP_INMU AND
IC_SBTINMUEB.COD_SBT_INM = IC_MODELOS.COD_SBT_INMU AND
IC_EMPRESA.COD_AGE = IC_PROMOCIONES.COD_AGE
/

I got this back

COD_RED COD_AGE COD_PRM
---------- ---------- ----------
2 325 1
2 596 2223
2 596 3331
2 600 57
2 600 1236
2 600 2345

So then in the same query I adde AND ROWNUM <= 3

I only got this back

COD_RED COD_AGE COD_PRM
---------- ---------- ----------
2 596 2223

I am a bit puzzled, any reason about this...?

Hi Chris

By the way I think I got it working, I will have to have a litte check on the result set but I think it´s correct. It worked after removing the partition part I was a bit confused about that part but it´s now clear.

I am doing the inner view way to compare the perfomance.


Thanks

That's because DISTINCT is pretty much the same as a GROUP BY - it is done *after* the resultset is built. ROWNUM is determined *as* the resultset is built. Therefore, you had


A B C RowNUM
1 1 1 1
1 1 1 2
1 1 1 3
1 2 1 4
...

Then, your ROWNUM <=3 gave you

A B C RowNUM
1 1 1 1
1 1 1 2
1 1 1 3


...and the distinct came back with

A B C
1 1 1

...tada :)

So have you tried the corrected version of the DENSE_RANK function yet?

- Chris

Oops, looks like we overlapped a little there - glad to see you got it worked out from the one side. Let us know if you run into trouble implementing the other side. And once you get it working, definitely let us know about the performance results!!

Thanks,

- Chris

Well the other side, the problem is about this DISCTINCT problem which is only returning me 1 row instead of 3
So I am getting wrong result sets
Adding a rownum column solved this

[Edited by pando on 03-07-2001 at 12:33 PM]

That's because you are trying to do the distinct on the outside. You need to do it on the inside.

going back to your original example (becaUSE IT'S IN A LANGUAGE i UNDERSTAND :) ) The trick is that you want a set of 3 States *that satisfy the query*. This entails 2 things

- You want to limit the initial query to 3 states
This could be accomplished throught the simplified FROM (SELECT * FROM (SELECT DISTINCT...) WHERE ROWNUM <=3) examples that people suggested
- However, you must also make sure that those 3 states are actually ones that will appear in the final results set. It would do you no good to pick California, for example, in that initial list, since it does not appear in the final list.


Therefore, your initial list must include all the restrictions of the final query. Look at this:

SELECT
---IM.COD_RED,
---IM.COD_AGE,
---IM.COD_PRM
FROM
---(
------SELECT
---------*
------FROM
---------(
------------SELECT DISTINCT
---------------IM.COD_RED,
---------------IM.COD_AGE,
---------------IM.COD_PRM
------------FROM
---------------IC_MODELOS------IM,
---------------IC_PROMOCIONES---IP
------------WHERE
---------------IP.COD_PAIS---------=---2---AND
---------------IP.COD_COM---------=---15---AND
---------------IP.COD_PRV---------=---28---AND
---------------IM.COD_TIP_INMU---=---8---AND
---------------IM.COD_MON---------=---1
---------------IP.COD_RED---------=---IM.COD_RED---------AND
---------------IP.COD_AGE---------=---IM.COD_AGE---------AND
---------------IP.COD_PRM---------=---IM.COD_PRM---------AND
---------)
------WHERE
---------ROWNUM <= 3
---)---T1
---IC_MODELOS------IM,
---IC_PROMOCIONES---IP,
---IC_EMPRESA------IE,
---IC_POBLACION---IPa,
---IC_TIPINMUEB---IT,
---IC_SBTINMUEB---ISb,
---IC_MONEDA------IMn
WHERE
---IP.COD_PAIS---------=---2------------------AND
---IP.COD_COM---------=---15------------------AND
---IP.COD_PRV---------=---28------------------AND
---IM.COD_TIP_INMU---=---8------------------AND
---IM.COD_MON---------=---1------------------AND
----- -------------------------------
---IM.COD_RED---------=---T1.COD_RED---------AND
---IM.COD_AGE---------=---T1.COD_AGE---------AND---
---IM.COD_PRM---------=---T1.COD_PRM---------AND
----- -------------------------------
---IP.COD_RED---------=---IM.COD_RED---------AND
---IP.COD_AGE---------=---IM.COD_AGE---------AND
---IP.COD_PRM---------=---IM.COD_PRM---------AND
---IMn.COD_MON---------=---IM.COD_MON---------AND
---IPa.COD_POB---------=---IP.COD_POB---------AND
---IT.COD_TIP_INM------=---IM.COD_TIP_INMU---AND
---ISb.COD_SBT_INM---=---IM.COD_SBT_INMU---AND
---IE.COD_AGE---------=---IP.COD_AGE


Now, I have assumed 2 things here:

- The IE, IPa, etc. tables are *non-restrictive*. In other words, they are simply used to provide additional fields to the result set, but do not remove any rows from the result set. *If this assumption is incorrect*, then you need to add those tables to the inner-select as well.

- I am also assuming that you have more in your SELECT list than you are showing us, otherwise, this entire exercise would not make sense. Going back to your original example, it would not make sense to go through all these hoops if you were only displaying the State column. You need at least a single detail column as well for all this to make sense. I'm assuming you chopped those columns out of the SELECT list for simplicity.

Anyway, give this a shot and let me know what you find.

- Chris

I have been trying it with the tables I have but getting strange result sets. Got 100968 rows of same values :P

However I think inner view method would be slower (and much) because of double checking of search criteria in the inner view and the parent query.

I still have to see why I am getting wrong result sets.

The analytic function seems much more elegant and clean compare with the one I am trying with inner views.

Thanks