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
AMAL1796Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AMAL1796Author Commented:
thx
0
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.