|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Grouping data...?I have the following recordset. The records EMP_ID equal to 001 are exactly the same except for the INSTANCES. There's only one EMP_ID 002 and 004 with INSTANCE = 0 (only one "instance" of the record). And finally there are two records with EMP_ID 003. I need to write a query that will only display 002 and 004 (only one record of each). Is this possible with one single query? I was thinking of grouping the data (and adding a count(*) ) into a temp table. Once I have this data, then I can go through the temp table and check which of the groupings only have one record (count(*) = 1 ). But is there another shorter way where it can be done in one query? EMP_ID DPT_ID CMP_ID INSTANCES ------------------------------------------------ 001 1A 22B 0 001 1A 22B 1 001 1A 22B 2 001 1A 22B 3 002 2D 22B 0 003 1D 22B 0 003 1D 22B 1 004 5C 22B 0 Thanks.
Show quote
"VMI" <V**@discussions.microsoft.com> wrote in message Take a look at the HAVING clause. I think it will do what you need it to.news:584D6979-F674-4660-9E8E-E38B83A4E1AC@microsoft.com... > Here's an example recordset because explaining it will be very long... > I have the following recordset. The records EMP_ID equal to 001 are > exactly > the same except for the INSTANCES. There's only one EMP_ID 002 and 004 > with > INSTANCE = 0 (only one "instance" of the record). And finally there are > two > records with EMP_ID 003. I need to write a query that will only display > 002 > and 004 (only one record of each). Is this possible with one single > query? > I was thinking of grouping the data (and adding a count(*) ) into a temp > table. Once I have this data, then I can go through the temp table and > check > which of the groupings only have one record (count(*) = 1 ). But is there > another shorter way where it can be done in one query? > > EMP_ID DPT_ID CMP_ID INSTANCES > ------------------------------------------------ > 001 1A 22B 0 > 001 1A 22B 1 > 001 1A 22B 2 > 001 1A 22B 3 > 002 2D 22B 0 > 003 1D 22B 0 > 003 1D 22B 1 > 004 5C 22B 0 > > > Thanks. Note not tested: But something like: CREATE TABLE Foo (EMP_ID char(3), DPT_ID char(2), CMP_ID char(3), INSTANCES int) INSERT Foo VALUES ('001','1A','22B',0) INSERT Foo VALUES ('001','1A','22B',1) INSERT Foo VALUES ('001','1A','22B', 2) INSERT Foo VALUES ('001','1A','22B', 3) INSERT Foo VALUES ('002','2D','22B', 0) INSERT Foo VALUES ('003','1D','22B', 0) INSERT Foo VALUES ('003','1D','22B', 1) INSERT Foo VALUES ('004','5C','22B', 0) SELECT * FROM Foo SELECT EMP_ID, DPT_ID, CMP_ID, COUNT(*) FROM Foo GROUP BY EMP_ID, DPT_ID, CMP_ID HAVING COUNT(*) = 1 DROP TABLE Foo Rick Sawtell MCT, MCSD, MCDBA On Thu, 27 Jul 2006 11:36:02 -0700, VMI wrote:
Show quote >Here's an example recordset because explaining it will be very long... Hi VMI,>I have the following recordset. The records EMP_ID equal to 001 are exactly >the same except for the INSTANCES. There's only one EMP_ID 002 and 004 with >INSTANCE = 0 (only one "instance" of the record). And finally there are two >records with EMP_ID 003. I need to write a query that will only display 002 >and 004 (only one record of each). Is this possible with one single query? >I was thinking of grouping the data (and adding a count(*) ) into a temp >table. Once I have this data, then I can go through the temp table and check >which of the groupings only have one record (count(*) = 1 ). But is there >another shorter way where it can be done in one query? > >EMP_ID DPT_ID CMP_ID INSTANCES >------------------------------------------------ >001 1A 22B 0 >001 1A 22B 1 >001 1A 22B 2 >001 1A 22B 3 >002 2D 22B 0 >003 1D 22B 0 >003 1D 22B 1 >004 5C 22B 0 SELECT EMP_ID, DPT_ID, CMP_ID, MAX(INSTANCES) FROM YourTable GROUP BY EMP_ID, DPT_ID, CMP_ID HAVING COUNT(*) = 1; (Untested - see www.aspfaq.com/5006 if you prefer a tested reply) -- Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics