• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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
0
AMAL1796
Asked:
AMAL1796
  • 4
  • 2
  • 2
  • +2
1 Solution
 
awking00Commented:
Can you post some sample data and the expected results?
0
 
sdstuberCommented:
can you post sample data and expected results?

I'm not entirely sure I understand what you're looking for
0
 
Christoffer SwanströmPartnerCommented:
Something like this?

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 = 1

Open in new window


Having some sample data would help though...
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
awking00Commented:
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);
0
 
awking00Commented:
Should have added s.client_id to the outer select.
0
 
AMAL1796Author Commented:
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
0
 
Christoffer SwanströmPartnerCommented:
The code I gave above should do it, please give it a try and let me know.
0
 
ianmills2002Commented:
Based on your example above, I think you need the SQL
SELECT
 C.CLIENT_ID
,GREATEST(S1.SCORE, S2.SCORE)   MAX_SCORE
,CASE WHEN S1.SCORE >= S2.SCORE THEN S1.LEVEL_ID ELSE S2.LEVEL_ID END   RISK_LEVEL_ID
FROM CLIENT C
     JOIN SCORE S1
     ON C.ID1 = S1.CLIENT_ID 
     JOIN SCORE S2
     ON C.ID2 = S2.CLIENT_ID

Open in new window


Regards,
Ian
0
 
AMAL1796Author Commented:
thx
0
 
awking00Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now