-
Probable soln...
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
-
Try This
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|