Only retrieve ONE of each - SQL Server Forums

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)

Only retrieve ONE of each

Author

Topic

sparkle
Starting Member

7 Posts

Posted - 2004-04-07 : 12:31:19

Hi,

I am trying to retrieve only one of each Department. To get a list of all departments, I am using a SELECT DISTINCT query. The results aren't what I want, though.

I am getting:

10 SALES department
4 PARTS department
2 ADMIN department
4 FINANCE deparment results

I only want

1 SALES department
1 PARTS department
1 ADMIN department
1 FINANCE deparment

Should I use a Count with a group by? How would I do this?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-07 : 12:33:44

Yes you could use a GROUP BY. How about posting some sample data for us though as well as the table layout? CREATE TABLE and INSERT INTO statements for these is best.

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-07 : 13:16:15

Without more info. SELECT DISTINCT department FROM myTable should work.

But I'm sure it's more complicated, and for that we'll need more information like Tara suggested.

sparkle
Starting Member

7 Posts

Posted - 2004-04-07 : 15:18:43

OK. I'm back.

Thanks for your responses;

Here is the code. I was trying to simplify the question in a generic way because the syntax is hairy:

SELECT DISTINCT gd.dbo.PERSONNEL.EMP_NUM, gd.dbo.luDivisions.DIVISION
FROM gd.dbo.PERSONNEL INNER JOIN
gd.dbo.[POS table archive] ON gd.dbo.PERSONNEL.EMP_NUM = gd.dbo.[POS table archive].EMP_NUM INNER JOIN
gd.dbo.luDivionsOrganizatons ON gd.dbo.[POS table archive].ORG_CODE = gd.dbo.luDivionsOrganizatons.ORG_CODE INNER JOIN
gd.dbo.luDivisions ON gd.dbo.luDivionsOrganizatons.[Division ID] = gd.dbo.luDivisions.[Division ID]
GROUP BY gd.dbo.PERSONNEL.EMP_NUM, gd.dbo.luDivisions.DIVISION

I am selecting from another DB which can't be manipulated. I only want to see ONE of each division without having to just make a seperate query with only divisionID and DIvision. I want to use this inside an ADO program I am creating.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-07 : 15:43:00

If you don't care about any employees in the divisions then just select DISTINCT gd.dbo.luDivisions.DIVISION and let the JOINs do the filtering for employees.

sparkle
Starting Member

7 Posts

Posted - 2004-04-07 : 15:48:14

YaHOO!

OK! Took out the first gd.dbo.emp_num.Personnel and it worked! Thank you DrymChser, I knew it would be something simple

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources