Help Needed For DISTINCT Field!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help Needed For DISTINCT Field!!!

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-10-29 : 00:04:52

Hi :
I am stuck with one query and its VERY VERY URGENT. I want to list all fields but only the records of those employees whose grade differs. At present the contents are somewhat like this :
Empid Grade Year Month
10 2 2002 1
10 2 2002 2
10 2 2002 3
10 2 2002 4
10 2 2002 5
10 2 2002 6
10 2 2002 7
10 3 2002 8
10 3 2002 9
10 3 2002 10

11 6 2002 5
11 6 2002 6
11 6 2002 7
11 6 2002 8
11 6 2002 9
11 5 2002 10

I don't want the duplicate grades, only those grades must be listed which differs. For eg: I want the records to be listed exactly Like this :
EmpId Grade Year Month
10 2 2002 7
10 3 2002 8

11 6 2002 9
11 5 2002 10

I hope U got my problem. I have tried using co-related sub-query and distinct but it doesn't work according to my requirements.
Please send me the solution as sooooooon as possible.
Thanks in Advance,
Marjo

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-10-29 : 01:02:27

See how this goes.. let us know if it doesnt return the required select set

SELECT DISTINCT Grade, EmpID, MAX([Month]) AS FieldMonth
FROM Table2
WHERE (EmpID IN (SELECT DISTINCT empid FROM table2))
GROUP BY Grade, EmpID

Cheerz,

Samrat

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-10-29 : 06:29:44

Hi Samrat:
Thanks for the solution. I tried it and it works perfectly, the only thing I have changed is :

Select EmpID,Grade,MIN(MONTH) AS FIELDMONTH
FROM Employee
Group By EmpID,Grade
order by EmpID

=> This gives me the distinct records for each grade.
Thanks A Lot Once Again.
Bye.
Marjo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources