| View previous topic :: View next topic |
| Author |
Message |
sakreg
Beginner
Joined: 28 Feb 2005
Posts: 80
Topics: 26
|
 Posted: Wed Jun 29, 2005 4:06 am Post subject: Eliminating Duplicates - DB2 Query |
 |
|
Hi
I have data in a Table as Below:
| Quote: |
Number Ver Code Eff Dt
*****************************
0710001 00 00008 2005-04-12
0710001 00 00015 2005-04-12
0710001 00 00017 2005-04-12
0710001 00 00028 2005-04-11
0710001 00 00028 2005-04-12
0710001 00 00112 2005-04-12
0710001 01 00008 2005-04-12
0710001 01 00015 2005-04-12
0710001 01 00017 2005-04-12
0710001 01 00028 2005-04-12
0710001 01 00112 2005-04-12
0710001 01 00112 2005-03-12
*****************************
|
I want to eliminate some rows using SQL Query based on the rule:
Rule: For a given Number and a Version, if there are duplicate Code, I need to pick-up only one based on the highest Eff Date.
Based on the data I have shown, I need to eliminate the one I have shown in Red and select the rest using a SQL Query.In Select Clause I need all the columns.
Any Suggestions, please let me know |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 29, 2005 5:35 am Post subject: |
|
|
sakreg,
Try this . I haven't tested it out
| Code: |
SELECT *
FROM TABLE
WHERE (Number,Ver,Eff_Dt) IN (SELECT Number
,Ver
,MAX(Eff_Dt)
FROM TABLE
GROUP BY NUMBER
,VER)
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
sakreg
Beginner
Joined: 28 Feb 2005
Posts: 80
Topics: 26
|
| Posted: Thu Jun 30, 2005 1:08 am Post subject: |
|
|
Thanks for your reply kolusu.
I got a SQL error code of -120. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
abalan76
Beginner
Joined: 01 Jul 2005
Posts: 1
Topics: 0
|
| Posted: Fri Jul 01, 2005 4:59 am Post subject: |
|
|
Sakreg,
Can you try this
First post , haven't tested it
| Code: |
SELECT * FROM TABLE A
WHERE EFF_DT = (SELECT MAX(EFF_DT)
FROM TABLE B
WHERE A.NUMBER = B.NUMBER
AND A.VER = B.VER)
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 01, 2005 5:17 am Post subject: |
|
|
abalan76,
You need an IN statement in your WHERE clause of your query as the subquery will result in a multiple rows.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
sakreg
Beginner
Joined: 28 Feb 2005
Posts: 80
Topics: 26
|
| Posted: Mon Jul 04, 2005 3:45 am Post subject: |
|
|
abalan76, Thanks for your time.
kolusu, I was wrong when I tried that before. Sorry for that. The query works like a charm. Thanks for that. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
