|
View previous topic :: View next topic
|
| Author |
Message |
Time2Live
New User
Joined: 27 Apr 2005
Posts: 43
Location: United States
|
|
|
|
Could I please get advise on a SPUFI where I am trying to Select the record
with matching data in Col1 and Col2 and the most recent date in Col3.
I have tried using Distinct on Col1 & Col2 and MAX on Col3, but it is not working for me.
Could anyone please advise? Thank you so much in advance.
This is the DB2 table:
| Code: |
Col1 Col2 Col3 Col4
17 101437 01/06/2018 344
17 101437 08/19/2017 344
17 150013 08/19/2017 344
17 150013 08/05/2017 350
17 9954099 05/05/2018 646
17 9954099 03/03/2018 344
17 9954099 10/21/2017 350
|
The results I want are no duplicates in Col1 & Col2 and max date in Col3.
| Code: |
17 101437 01/06/2018 344
17 150013 08/19/2017 344
17 9954099 05/05/2018 646
|
I have tried SPUFIs like this:
| Code: |
SELECT DISTINCT
COL1,
COL2,
MAX(COL3),
COL4
FROM DB2TEST.TABLE
GROUP BY COL1, COL2;
|
|
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| You say that you do not want duplicates in col 1 but your desired output shows all values in col 1 to be the same. f you meant no duplicates of conacatenated col1 and col2 then you should say so and you should concatenate them in your query. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
You need all columns in Group by as that you have it in SELECT (without columns used in aggregate function). You need to think of a different approach. Hint could be to have a correlated query to find max date for the outer select to get that particular row.
Moreover , Distinct and Group by is ambiguous. |
|
| Back to top |
|
|
Time2Live
New User
Joined: 27 Apr 2005
Posts: 43
Location: United States
|
|
|
|
Hi all,
I did mean to say that the concat of col1 and col2 should be unique.
They are the 2 separate fields that are the table's key.
I tried what Rohit hinted, but dont know how to fix the error.
| Code: |
SELECT Col1,
Col2,
Col3,
Col4
FROM DB2TEST.TABLE OUTR
WHERE Col3 >
(SELECT AVG(Col3)
FROM DB2TEST.TABLE INNR
WHERE INNR.col1 = OUTR.col1
AND INNR.col2 = OUTR.col2);
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF AVG IS INVALID |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Why AVG when you needed MAX in SELECT ? You need latest entry to be picked up or that's no more the situation. |
|
| Back to top |
|
|
Time2Live
New User
Joined: 27 Apr 2005
Posts: 43
Location: United States
|
|
|
|
Hi All,
Our DBA just got back from vacation and showed me how to do what I really wanted to do which is this correlated query.
Thank you all for your time! 
| Code: |
SELECT A.COL1,
A.COL2,
C.COL3,
C.COL4
FROM DB2TEST.TABLE2 A, DB2TEST.TABLE1 C
WHERE A.COL1 = C.COL1
AND A.COL2 = C.COL2
AND C.COL4 =
(SELECT MAX(B.COL4)
FROM DB2TEST.TABLE1 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2);
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Okay. But why do you have to use DB2TEST.TABLE1 twice when one can do the work? Plus you are doing MAX on col4 when in initial post you said you want MAX of col3 and that won't assure you the same output every time you run than what you listed above as expected output. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
why do you have to use DB2TEST.TABLE1 twice when one can do the work
|
Rohit, do you mind posting a sample? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Arun, DB2TEST.TABLE2 table is not required at all since all the information needed in the final output is present in DB2TEST.TABLE1. Why join DB2TEST.TABLE2 unnecessarily? If TS has a requirement to check if the row present in DB2TEST.TABLE2 or not then just EXISTS on DB2TEST.TABLE2 can be used later point.
This is possibly how it can be done using single use of DB2TEST.TABLE1
| Code: |
select final_t.col1, final_t.col2, final_t.col3, final_t.col4
(SELECT col1,col2,col3,col4,
ROW_NUMBER() OVER
(PARTITION BY col1,col2 ORDER BY col4 DESC) as col5
FROM DB2TEST.TABLE1) final_T
where col5 = 1 |
If TS wants those rows which makes match with DB2TEST.TABLE2 then just twist like this and its done.
| Code: |
select final_t.col1, final_t.col2, final_t.col3, final_t.col4
(SELECT col1,col2,col3,col4,
ROW_NUMBER() OVER
(PARTITION BY col1,col2 ORDER BY col4 DESC) as col5
FROM DB2TEST.TABLE1) final_T
where col5 = 1
and exists (select 1 from DB2TEST.TABLE2 b
where final_t.col1 = b.col1
and final_t.col2 = b.col2)
|
|
|
| Back to top |
|
|
|
|
