Get higher education from SQL query

Question

0.00/5 (No votes)

See more:

i have user list

where userid, username , education , level ,levelid details i have

leveltbl

b.c.a  1
m.c.a  2
phd    3

Users

1  jaydeep  b.c.a  1
2  jaydeep  m.c.a  2
3  dhrumil  b.c.a  1
4  jayanti  PHD    3
5  jayanti  M.C.a  2

what output i required (only user higher education)

2  jaydeep  m.c.a  2
3  dhrumil  b.c.a  1
4  jayanti  PHD    3

What I have tried:

select * from users where max( ) ;(dont know what i do here )

Posted 8-Nov-17 20:56pm

Jaydeep Shah

Updated 8-Nov-17 22:27pm

OriginalGriff

v3

Add a Solution

Comments

Santosh kumar Pithani 9-Nov-17 4:51am

Your not clearly mentioned about "leveltbl" table relation so update your question one's more.

2 solutions

Solution 1

The first thing to note is that you aren't using both tables: Your actual data should be
leveltbl

Desc     NVARCHAR
ID       INT, PRIMARY KEY

Which is what you have.
Users

ID       INT, probably IDENTITY, PRIMARY KEY
UName    NVARCHAR

With a third table to connect them:
UserEducation

ID       INT, IDENTITY
UID      INT, FOREIGN KEY to Users.ID
LID      INT, FOREIGN KEY to LevelTbl.ID
Grade    INT

That way, you aren't storing duplicate information - which both wastes space, and complicates your job because it allows errors to creep in, as they do in your data: Is "m.c.a" the same as "M.C.a"?

Then to access the data, you use a JOIN:

SQL

SELECT u.UName, l.Desc, e.Grade FROM UserEducation e
JOIN Users u ON e.UID = u.ID
JOIN LevelTbl l ON e.LID = l.ID

That give you your "raw" data, and it's pretty simple to use GROUP BY and MAX to get exactly the result you want from that.

Posted 8-Nov-17 21:18pm

OriginalGriff

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)

Santosh kumar Pithani · Accepted Answer · 2017-11-08T22:27:00

Solution 2

CREATE  TABLE #TEMP(userid INT, username VARCHAR(50), education VARCHAR(10) , level INT);
INSERT INTO #TEMP 
VALUES(1,'jaydeep','b.c.a',1),
      (2,'jaydeep','m.c.a',2),
      (3,'dhrumil','b.c.a',1),
      (4,'jayanti','PHD', 3),
      (5,'jayanti','M.C.a',2);

WITH CTE AS (
  SELECT *,ROW_NUMBER()OVER(PARTITION BY USerNAme ORDER BY LEVEL DESC) lvl 
     FROM #temp
            )
SELECT userid, username,education,level 
  FROM CTE
     WHERE LVL=1 ORDER BY USERID ;

--------------------------------------------
userid	username	education	level
---------------------------------------------
2	jaydeep	m.c.a	2
3	dhrumil	b.c.a	1
4	jayanti	PHD	3

Posted 8-Nov-17 22:27pm

Santosh kumar Pithani

Comments

F-ES Sitecore 9-Nov-17 5:07am

I doubt the course he is getting us to do his homework for has got as far as CTEs :)

Santosh kumar Pithani 9-Nov-17 7:29am

??

F-ES Sitecore 9-Nov-17 7:31am

This is obviously his homework that he can't do himself, if he submits a solution using CTEs his tutor is going to know for sure he has cheated and got the solution from someone far more experienced.

Santosh kumar Pithani 9-Nov-17 22:36pm

:)

Jaydeep Shah 12-Nov-17 12:05pm

perfect solution for me. thanks

Santosh kumar Pithani 12-Nov-17 22:54pm

Welcome Jaydeep!

Jaydeep Shah 12-Nov-17 12:27pm

cheated ??? :) @F-ES Sitecore