Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 5th, 2009, 10:06 AM
Registered User
 
Join Date: Feb 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Please help

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

Last edited by Gerald; February 5th, 2009 at 10:12 AM..
 
Old February 5th, 2009, 11:43 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
 
Old February 5th, 2009, 12:14 PM
Registered User
 
Join Date: Feb 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by dparsons View Post
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 February 5th, 2009, 03:36 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old February 6th, 2009, 07:13 AM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Simpler...

SELECT departmentid,
empname,
existsyn
FROM departments
ORDERBY departmentid,
existsyn DESC,
empname DESC
 
Old February 6th, 2009, 08:31 AM
Authorized User
 
Join Date: Jan 2009
Location: thanjavur,india
Posts: 43
Thanks: 0
Thanked 2 Times in 2 Posts
Default from dinesh

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.
 
Old February 6th, 2009, 08:43 AM
Authorized User
 
Join Date: Jan 2009
Location: thanjavur,india
Posts: 43
Thanks: 0
Thanked 2 Times in 2 Posts
Default from dinesh

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
 
Old February 6th, 2009, 09:11 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default No Sort Order

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
 
Old February 6th, 2009, 09:18 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default A possible solution

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)
__________________
--Jeff Moden
 
Old February 6th, 2009, 09:19 AM
Authorized User
 
Join Date: Jan 2009
Location: thanjavur,india
Posts: 43
Thanks: 0
Thanked 2 Times in 2 Posts
Default from dinesh

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








Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.