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
and B.DEPT_ID in
(select distinct DEPT_ID from LOC where rownum<=3)
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,
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:
------DENSE_RANK() OVER (ORDER BY T1.COLA)
---MYRANK <= 3
Hope this helps,
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!!
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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
[QUOTE][i]Originally posted by chrisrlong [/i]
[B]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
Back again but with problems, I am not sure if I partitioned the wrong columns.
The query is
SELECT * FROM
DENSE_RANK() OVER (PARTITION BY
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
WHERE MYRANK <= 3
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: