AMAL1796
asked on
get the record of the maximum
Hi,
I have two tables : the first one is called client, the second is name score. in client table i have 4 columns (client id, flag,id1,id2), the score table contains : client id, score and level id.
I must return a max score and risk_level_id of the max score where (client_id2 = score_client_id or client_id1= score_client_id)
Thank you for your help !
Kind Regards
Ahlam
I have two tables : the first one is called client, the second is name score. in client table i have 4 columns (client id, flag,id1,id2), the score table contains : client id, score and level id.
I must return a max score and risk_level_id of the max score where (client_id2 = score_client_id or client_id1= score_client_id)
Thank you for your help !
Kind Regards
Ahlam
awking00
Can you post some sample data and the expected results?
can you post sample data and expected results?
I'm not entirely sure I understand what you're looking for
I'm not entirely sure I understand what you're looking for
Something like this?
Having some sample data would help though...
SELECT
client_id
,score
,level_id
FROM
(SELECT
cl.client_id
,sc.score
,sc.level_id
,RANK() OVER(PARTITION BY cl.client_id ORDER BY sc.score DESC) AS rnk
FROM
client cl
INNER JOIN
score sc
ON
(cl.id1 = sc.client_id OR cl.id2 = sc.client_id)
) asd
WHERE
rnk = 1Having some sample data would help though...
Perhaps something like the following:
select s.score, s.level_id from
client c,
(select client_id,score, level_id,
row_number() over (partition by client_id order by score desc) rn
from score) s
where s.rn = 1 and
(c.id1 = s.client_id or c.id2 - s.client_id);
select s.score, s.level_id from
client c,
(select client_id,score, level_id,
row_number() over (partition by client_id order by score desc) rn
from score) s
where s.rn = 1 and
(c.id1 = s.client_id or c.id2 - s.client_id);
Should have added s.client_id to the outer select.
ASKER
Hi,
Here by an exemple :
Client 1 : has two linked ids id1 and id2
In the score table I have these values :
Id1 : score1 level1
Id2 : score2 level2
I need to get the level of the max score for client 1
Means if score1 =33 and score2 = 22, I need to have
the level1 as risk level for client 1.
Hope this is clear ...
Thanks in advance.
Amal Merzouk
Here by an exemple :
Client 1 : has two linked ids id1 and id2
In the score table I have these values :
Id1 : score1 level1
Id2 : score2 level2
I need to get the level of the max score for client 1
Means if score1 =33 and score2 = 22, I need to have
the level1 as risk level for client 1.
Hope this is clear ...
Thanks in advance.
Amal Merzouk
The code I gave above should do it, please give it a try and let me know.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thx
Sorry, I didn't get to see your example (left early for a dr's appt) but, other that the typo of the minus sign instead of an equals sign, the analytical query I showed (as did tosse's) should perform better than the self join.