How to write sql query to fetch record according to this scenrio

Question

1.00/5 (1 vote)

See more:

Hi guys i have one view table called VW_VW_Academics

that view consists of

Position   MaxPoints
 1          48
 2          24
 3          16
 4          12

In My Maintable i have fldAvgMarks,fldHouseID table name is tblChampionsThrophy

My Query is

SQL

SELECT A.fldAvgMarks, A.fldHouseID,B.MaxPoints
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY fldAvgMarks DESC) AS Position, *
    FROM RMS.tblChampionsThrophy
    ) AS A inner JOIN (
            SELECT Position, MaxPoints
            FROM VW_Academics) AS B ON A.Position=B.Position

I'M getting output

fldAvgMarks fldHouseID  MaxPoints
40	    HN04	48
40	    HN05	24-----> here it should come 48 only b'coz fldAvgMarks both HN04,5 r 40
20	    HN03	16
10	    HN02	12

Here Output is Partially correct.. How to do that?

Can any Pls Suggest me.

Posted 2-Apr-13 18:01pm

Salt Pepper

Updated 2-Apr-13 19:08pm

Aarti Meswania

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Answer 1 · 2013-04-02T18:27:00

Solution 1

Hi,

try by making use of MAX function for MaxPoints like below.

SQL

SELECT A.fldAvgMarks, A.fldHouseID,MAX(B.MaxPoints) 'MaxPoints'
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY fldAvgMarks DESC) AS Position, *
    FROM RMS.tblChampionsThrophy
    ) AS A inner JOIN (
            SELECT Position, MaxPoints
            FROM VW_Academics) AS B ON A.Position=B.Position
GROUP BY A.fldAvgMarks, A.fldHouseID

hope it works.

Posted 2-Apr-13 18:27pm

Karthik Harve

Comments

Maciej Los 3-Apr-13 1:59am

See my answer (after update). I use RANK() instead of ROW_NUMBER function ;)

Karthik Harve 3-Apr-13 2:11am

Yes, that is the option. Thanks for telling me. now i recalled that function.

Maciej Los 3-Apr-13 2:27am

Karthik, i'm affraid that above query wont work, because MaxPoint are getting from different table. For the second position it returns 24, but should return 48. Please, test it and correct to avoid down-voting.

Karthik Harve 3-Apr-13 2:35am

Ya i'll do bro. i'll check once again.

Maciej Los · Answer 2 · 2013-04-02T19:56:00

Solution 2

Do not repost!!!

Answer is here: sql query to fetch particular field and assign value[^]

Posted 2-Apr-13 19:56pm

Maciej Los