DISTINCT for only some fields in query, not all? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

DISTINCT for only some fields in query, not all?

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2003-12-30 : 07:49:42

I have a query which returns, let's say, 10 fields.

I want to get distinct records, but only for maybe 4 of those fields.

How do I code this? Currently I have this query:

SELECT DISTINCT
Tasks.TaskID,
Tasks.TaskDescription,
Tasks.Customer,
Tasks.TaskType,
TaskDates.Resource,
TaskDates.ID,
TaskDates.CallFirstRequired,
TaskDates.Confirmed,
TaskDates.Locked,
TaskDates.Status,
TaskDates.DateValue,
TaskDates.Resource
FROM Tasks
INNER JOIN TaskDates ON Tasks.TaskID = TaskDates.TaskID

which works fine except it uses all the fields to return the distinct records.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-30 : 08:30:35

There are many ways to get this job done.

If you post your table DDL and some sample data, we'll cook up an exact query.

Hard to answer without knowing which 4 fields you want distinct , here's an example.

SELECT DISTINCT
Tasks.TaskID,
Tasks.TaskDescription,
Tasks.Customer,
Tasks.TaskType,
TaskDates.Resource,
TaskDates.ID,
TaskDates.CallFirstRequired,
TaskDates.Confirmed,
TaskDates.Locked,
TaskDates.Status,
TaskDates.DateValue,
TaskDates.Resource
FROM Tasks
INNER JOIN TaskDates ON Tasks.TaskID = TaskDates.TaskID
WHERE TaskID IN (SELECT MAX(TaskID) As TaskID -- Assume TaskID is a PK
From MyTable
GROUP BY TaskDescription, Customer, TaskType, Resource) -- Arbitrary 4 fields

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-30 : 09:21:27

Something can not be DISTINCT if a row has multiple values.

You need to make a chice about what you want back...

understanding also that the attributes of a row, belong to that row, so mixing and matching data between rows could be misleading...

So you can't say, "I want the DISTINCT values from cols 1,2,3 and the remaing 5 I don't care what I get"

You have to choose.

Take for example the following example (cut and paste in to QA to see it work)



USE Northwind
GO

CREATE TABLE myTable99 (Col1 int, Col2 varchar(10), Col3 datetime)
GO

INSERT INTO myTable99 (Col1, Col2, Col3)
SELECT 1, 'A', '01/01/2003' UNION ALL
SELECT 1, 'B', '01/01/2003' UNION ALL
SELECT 1, 'C', '01/01/2003' UNION ALL
SELECT 2, 'A', '01/01/2003' UNION ALL
SELECT 2, 'B', '01/01/2003' UNION ALL
SELECT 2, 'C', '01/01/2003' UNION ALL
SELECT 1, 'A', '12/01/2003' UNION ALL
SELECT 2, 'A', '12/01/2003'
GO

SELECT DISTINCT Col1, Col2 FROM myTable99

SELECT DISTINCT Col1, Col2, Col3 FROM myTable99

SELECT DISTINCT Col1, Col2, MAX(Col3) FROM myTable99
GROUP BY Col1, Col2
GO

DROP TABLE MyTable99
GO

Which one would be "right" for you?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources