13,736,882 members
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
Santosh kumar Pithani 9-Nov-17 4:51am

## 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
)
FROM CTE
WHERE LVL=1 ORDER BY USERID ;

--------------------------------------------
---------------------------------------------
2	jaydeep	m.c.a	2
3	dhrumil	b.c.a	1
4	jayanti	PHD	3```
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

## 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
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 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.

Top Experts
Last 24hrsThis month
 OriginalGriff 139 CHill60 125 Richard MacCutchan 75 Patrice T 54 Gabriel 2 50
 OriginalGriff 5,488 Richard MacCutchan 1,974 Patrice T 1,469 CPallini 1,249 Dave Kreskowiak 1,170