Distinct Query Problem - 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)

Distinct Query Problem

Author

Topic

jaford
Starting Member

14 Posts

Posted - 2002-08-18 : 13:57:27

I need to come up with a query that select distinct rows here is my table and data:

CREATE TABLE [tblRAW_Data] (
[tid] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (255) NULL ,
[week] [varchar] (255) NULL ,
[SKU] [varchar] (255) NULL ,
[QTY] [int] NULL ,
[Name] [varchar] (255) NULL ,
[empid] [varchar] (255) NULL ,
CONSTRAINT [PK_tblRAW_Data] PRIMARY KEY CLUSTERED
(
[tid]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert into tblRAW_Data values( 'Emp','200201','123','1','Test1','1234')
Go
Insert into tblRAW_Data values( 'Emp','200201','123','1','Test1','1234')
Go
Insert into tblRAW_Data values( 'Emp','200201','123','1','Test2','1234')
Go
Insert into tblRAW_Data values( 'Emp','200201','122','1','Test1','1234')
Go
Insert into tblRAW_Data values( 'Emp','200201','123','1','Test1','1235')
Go

I need a query that selects all columns but based on distinct sku,empid,week combinations. obviously, there is some type of subquery in the Where arguement.....i just can't seem to figure it out.

Thanks!

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-18 : 15:00:52

What result do you want, given the sample data? Talking about distinct on a subset of the values of a record in a result set does not make any sense to me.

jaford
Starting Member

14 Posts

Posted - 2002-08-18 : 15:23:20

What I'm needing is a result set that includes only distinct week,sku and empid combinations. For example with this data the result set should include:
TID TYPE WEEK SKU QTY NAME EMPID
1 Emp 200201 123 1 Test1 1234
4 Emp 200201 122 1 Test1 1234
5 Emp 200201 123 1 Test1 1235

TID's 2 and 3 would not be included because their not distinct combinations of week, qty and empid....they match TID 1.

Make sense? Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-18 : 15:41:11

SELECT * FROM tblRAW_Data T
INNER JOIN (SELECT Min(tid) MinID FROM tblRAW_Data GROUP BY week, sku, empid) AS A
ON T.tid=A.MinID

Edited by - robvolk on 08/18/2002 15:41:32

jaford
Starting Member

14 Posts

Posted - 2002-08-18 : 16:08:03

Perfect...THANK YOU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources