Click here to Skip to main content
13,736,882 members
Rate this:
 
Please Sign up or sign in to vote.
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
Updated 8-Nov-17 22:27pm
v3
Comments
Santosh kumar Pithani 9-Nov-17 4:51am
   
Your not clearly mentioned about "leveltbl" table relation so update your question one's more.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
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.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 9 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100