Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Fri May 20, 2005 2:51 pm Post subject:
Bullfighter,
If you want to pick up distinct values of col1 ,col2 and col3 you should use a scalar/column function on the other 2 columns.
I will explain with an example. let us assume that your table looks like this
Code:
---------+---------+---------+---------+----
COL1 COL2 COL3 COL4 COL5
---------+---------+---------+---------+----
ABC BB C 1 2
ABC BB C 3 4
ABC BB C 5 6
DEF CC D 7 8
DEF CC D 9 0
DEF CC D 1 2
Now if you want to pick an unique value for col1= abc, what values of col4 and col5 will you pick?
That is where you will have a problem. do you want to pick the max or min for col4 and col5?
Tell us which column you want to pick col4 and col5 and we will give you a solution.
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Fri May 20, 2005 3:25 pm Post subject:
Quote:
I will do an ORDER BY COL5 DESC and pick what ever comes in the first COL4 and COL5 for unique ( COL1, COL2, COL3 )
Code:
SELECT COL1
,COL2
,COL3
,COL4
,COL5
FROM TAB1.TABLE
WHERE (COL1,COL2,COL3,COL5) IN (SELECT COL1
,COL2
,COL3
,MAX(COL5)
FROM TAB1.TABLE
WHERE COL1 = 'ABC'
GROUP BY COL1
,COL2
,COL3)
;
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
