| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-16-08, 16:52
|
|
Registered User
|
|
Join Date: Apr 2007
Posts: 51
|
|
|
"Distinct" queries
|
|
Friends,
I am facing an issue in my queries.
Query 1 and 2 is expected to give the same results. But "Distinct" in Query 2 is not working and it results in exactly twice the rows of result given by Query 1.
How to resolve it?
I am giving both the queries here.
Query 1:
SELECT DISTINCT A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT
FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C
WHERE A.NUM_PART = C.NUM_PART AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS AND
A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR = C.DTE_MODL_YR
Resultset 1: It consists of 13 rows ( required resultset )
Query 2:
SELECT * FROM (SELECT DISTINCT A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT, ROWNUMBER() OVER (ORDER BY A.DTE_MODL_YR DESC) AS RN FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01' AND A.NUM_PART = C.NUM_PART AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS AND A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ') AS RES WHERE RN between 0 and 30;
Resultset 2: It consists of 26 rows ( with duplicates )
|
|
10-16-08, 17:09
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Select distinct will return only unique rows. This means the entire row. If any part is different, then it will be included. You have row_number() in your query, so every row will be different.
Andy
|
|
10-16-08, 17:12
|
|
Registered User
|
|
Join Date: Apr 2007
Posts: 51
|
|
|
|
I have no requirement to display the rownumber. I am using it only to select a particular range of values ( rows between 20 and 30 etc)
So, is it possible to retrieve only distinct rows?
|
|
10-16-08, 17:27
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
What happens is this: You use the OLAP function ROWNUMBER() that produces a distinct number for each row. If you apply DISTINCT on that, there will not by any duplicates (due to the different row numbers) so that nothing gets removed.
What you have to do is to do the DISTINCT first, then filter based on the row number.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
10-17-08, 13:41
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
What about
Code:
SELECT DISTINCT A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT
FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C
WHERE A.DTE_MODL_YR = B.DTE_MODL_YR
AND B.DTE_MODL_YR = C.DTE_MODL_YR
AND A.DTE_MODL_YR = C.DTE_MODL_YR
AND A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01'
AND A.NUM_PART = C.NUM_PART
AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS
AND A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ'
AND ROWNUMBER() OVER (ORDER BY A.DTE_MODL_YR DESC) between 0 and 30;
That is, just remove the column causing the duplicates to be non-duplicate, viz. the rownumber() column.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
|
10-17-08, 13:51
|
|
Registered User
|
|
Join Date: Apr 2007
Posts: 51
|
|
The above code gives
SQL0120N Invalid use of an aggregate function or OLAP function.
Anyways Thank you so much for reply.
I tried the below query. Its working fine, retrieving the required resultset without duplicates and it enables searches within ranges ( 1 to 30 , 5 to 10 rows etc) also:
select * from (select dte_modl_yr,num_part, amt_ecr_part_retl,TXT_ECR_PART_COM,DES_ECR_VEMSS_E XT, rownumber() over (order by dte_modl_yr desc) as rn from (SELECT distinct A.NUM_PART, a.dte_modl_yr, A.AMT_ECR_PART_RETL, TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01' AND A.NUM_PART = C.NUM_PART AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS AND A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ')as res) as res1 where AMT_ECR_PART_RETL IS NULL AND rn between 0 and 30;
|
|
10-17-08, 15:32
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
What about
Code:
SELECT DISTINCT *
FROM (SELECT NUM_PART, AMT_ECR_PART_RETL, TXT_ECR_PART_COM, DES_ECR_VEMSS_EXT
FROM
(SELECT ROWNUMBER() OVER (ORDER BY A.DTE_MODL_YR DESC) AS rn,
A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT
FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C
WHERE A.DTE_MODL_YR = B.DTE_MODL_YR
AND B.DTE_MODL_YR = C.DTE_MODL_YR
AND A.DTE_MODL_YR = C.DTE_MODL_YR
AND A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01'
AND A.NUM_PART = C.NUM_PART
AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS
AND A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ') x
WHERE rn between 0 and 30) y
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
|
10-17-08, 16:06
|
|
Registered User
|
|
Join Date: Apr 2007
Posts: 51
|
|
Great! Its working good with the required distinct result set. Thanks a lot 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
"Distinct" queries
