Please help

Gerald · February 5th, 2009, 10:06 AM

sample data :
I have a table called departments and sample data in that table is as below

departmentid empname existsyn
1 123 1
1 111 1
1 124 1
2 224 0
2 542 1
3 999 0
3 888 0
3 777 1
.......
i want output as

departmentid empname existyn
1 123 1
1 111 1
2 224 0
2 542 1
3 999 0
3 888 0

i.e., First two employees details of each department.

Iam using sql server 2000.

Please help me...

dparsons · February 5th, 2009, 11:43 AM

I am not sure I am following. You are excluding different employees but you aren't excluding them based on the same critera. The employees you want to exlude are in different departments and also have different values for the existssyn fields.

Really, if this is what you want, a simple select like so would do it:

SELECT * from table where Empname not In (124, 777)

hth.
-Doug

Gerald · February 5th, 2009, 12:14 PM

Quote:

Originally Posted by dparsons

I am not sure I am following. You are excluding different employees but you aren't excluding them based on the same critera. The employees you want to exlude are in different departments and also have different values for the existssyn fields.

Really, if this is what you want, a simple select like so would do it:

SELECT * from table where Empname not In (124, 777)

hth.
-Doug

Hi,

Thanks for reply...But i just gave sample data. Each department may have 1 to n employees and Dont bother about activeyn field. Just Bring top 2 records for each department. I cant exclude the hundreds of records as u specified earlier by using NOT IN

Hope iam explained u clearly.....

Regards,
Gerald.

Old Pedant · February 5th, 2009, 03:36 PM

There's probably a simpler way to do this, but I can see one way right off the top of my head:

Code:

SELECT ... FROM table WHERE empname IN 
(
     SELECT MIN(empname) FROM table GROUP BY departmentid
         UNION 
     SELECT MIN(empname) FROM table 
     WHERE empname NOT IN 
     ( 
             SELECT MIN(empname) FROM table GROUP BY departmentid 
     ) GROUP BY departmentid
) ORDER BY departmentid, empname

You didn't say how you wanted to select the two employees, so I just took the first two, alphabetically.

Peso · February 6th, 2009, 07:13 AM

Simpler...

SELECT departmentid,
empname,
existsyn
FROM departments
ORDERBY departmentid,
existsyn DESC,
empname DESC

dinesh · February 6th, 2009, 08:31 AM

hi old pedant,
the result of ur query will be

1 111 1
1 123 1
2 224 0
2 542 0
3 777 0
3 888 0

but what he wants is the first two of each dept,
1 123 1
1 111 1
2 224 0
2 542 1
3 999 0
3 888 0

in ur query result, the second and third employee for department 3 will get. but he needs the first and second.

dinesh · February 6th, 2009, 08:43 AM

why dont u use this,

selecttop 2 *from departments where departmentid=1
union
selecttop 2 *from departments where departmentid=2
union
selecttop 2 *from departments where departmentid=3

Jeff Moden · February 6th, 2009, 09:11 AM

The problem here is, nothing defines a sort order and, therefore, there is no guarantee that what you SEE as the first two of a given department are what will be SELECTed as the first two of a given department. This problem should be, "Select ANY two from each department".

Jeff Moden · February 6th, 2009, 09:18 AM

For the record, I hate correlated subqueries because you can't run them separately for troubleshooting, but this will do the trick provided that EmpName is unique each department...

Code:

 
 SELECT *
   FROM Departments d1
  WHERE EmpName IN (SELECT TOP 2 EmpName 
                      FROM Departments d2 
                     WHERE d2.DepartmentID = d1.DepartmentID)

dinesh · February 6th, 2009, 09:19 AM

you mean "any two" rite, why dont it be the first two?