Select Distinct for multiple columns

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)

Select Distinct for multiple columns

Author

Topic

mathie
Starting Member

4 Posts

Posted - 2010-02-05 : 05:06:55

Hi All,
I have a table with fields Repairer, Description, Regno, Make, PDate and Amount. I want to display a distinct value for the Description and Repairer using PDate i.e using the most recent Pdate. I have checked many articles on ‘Select Distinct for multiple columns’ but doesn’t seems to be satisfied.
Help will be appreciated

Tables Sample
DESCRIPTION | REPAIRER | REGNO | MAKE | PDATE | AMOUNT |
OIL FILTER | ABC | RR1 | MM1 | 01/02/2010 | 300 |
OIL FILTER | ABC | RR2 | MM2 | 01/01/2010 | 200 |
FUEL FILTER | ABC | RR1 | MM1 | 01/02/2010 | 100 |
OIL FILTER | XYZ | RR1 | MM1 | 01/02/2010 | 300 |
FUEL FILTER | XYZ | RR1 | MM1 | 01/02/2010 | 100 |
ENGINE OIL | ABC | RR2 | MM2 | 01/02/2010 | 150 |
OIL FILTER | ABC | RR3 | MM3 | 13/01/2010 | 200 |
ENGINE OIL | ABC | RR1 | MM1 | 01/01/2010 | 150 |
FUEL FILTER | XYZ | RR4 | MM4 | 01/01/2010 | 50 |
OIL FILTER | XYZ | RR1 | MM1 | 01/01/2010 | 200 |

Expected Answer
ENGINE OIL | ABC | RR2 | MM2 | 01/02/2010 | 150 |
FUEL FILTER | ABC | RR1 | MM1 | 01/02/2010 | 100 |
FUEL FILTER | XYZ | RR1 | MM1 | 01/02/2010 | 100 |
OIL FILTER | ABC | RR1 | MM1 | 01/02/2010 | 300 |
OIL FILTER | XYZ | RR1 | MM1 | 01/02/2010 | 300 |

Mathie

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 05:12:50

maybe it is good enough to use max() for the not grouped columns?

No, you're never too old to Yak'n'Roll if you're too young to die.

mathie
Starting Member

4 Posts

Posted - 2010-02-05 : 05:38:27

MAX?
Can I have a sample SQL Statement based on the sample Table given.

Mathie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 05:43:47

Webfred -- I don't think so -- I think the op wants the most recent PDdate per description and repairer. I could see a problem if costs for the same description could be different -- you could bring back the wrong value for the pay date.

Something like this would probably be safer

/*Tables Sample
DESCRIPTION |	REPAIRER |	REGNO |	MAKE |	PDATE |	AMOUNT |
OIL FILTER |	ABC |	RR1 |	MM1 |	01/02/2010 |	300 |
OIL FILTER |	ABC |	RR2 |	MM2 |	01/01/2010 |	200 |
FUEL FILTER |	ABC |	RR1 |	MM1 |	01/02/2010 |	100 |
OIL FILTER |	XYZ |	RR1 |	MM1 |	01/02/2010 |	300 |
FUEL FILTER |	XYZ |	RR1 |	MM1 |	01/02/2010 |	100 |
ENGINE OIL |	ABC |	RR2 |	MM2 |	01/02/2010 |	150 |
OIL FILTER |	ABC |	RR3 |	MM3 |	13/01/2010 |	200 |
ENGINE OIL |	ABC |	RR1 |	MM1 |	01/01/2010 |	150 |
FUEL FILTER |	XYZ |	RR4 |	MM4 |	01/01/2010 |	50 |
OIL FILTER |	XYZ |	RR1 |	MM1 |	01/01/2010 |	200 |


Expected Answer
ENGINE OIL |	ABC |	RR2 |	MM2 |	01/02/2010 |	150 |
FUEL FILTER |	ABC |	RR1 |	MM1 |	01/02/2010 |	100 |
FUEL FILTER |	XYZ |	RR1 |	MM1 |	01/02/2010 |	100 |
OIL FILTER |	ABC |	RR1 |	MM1 |	01/02/2010 |	300 |
OIL FILTER |	XYZ |	RR1 |	MM1 |	01/02/2010 |	300 |
*/

DECLARE @rawData TABLE (
	[description] VARCHAR(255)
	, [repairer] CHAR(3)
	, [regno] CHAR(3)
	, [make] CHAR(3)
	, [pdate] DATETIME
	, [amount] MONEY
	)

INSERT @rawData
          SELECT 'OIL FILTER' ,	'ABC' ,	'RR1' ,	'MM1' ,	'20100201', 300
UNION ALL SELECT 'OIL FILTER' ,	'ABC' ,	'RR2' ,	'MM2' ,	'20100101',	200
UNION ALL SELECT 'FUEL FILTER' ,	'ABC' ,	'RR1' ,	'MM1' ,	'20100201',	100
UNION ALL SELECT 'OIL FILTER' ,	'XYZ' ,	'RR1' ,	'MM1' ,	'20100201',	300
UNION ALL SELECT 'FUEL FILTER' ,	'XYZ' ,	'RR1' ,	'MM1' ,	'20100201',	100
UNION ALL SELECT 'ENGINE OIL' ,	'ABC' ,	'RR2',	'MM2' ,	'20100201',	150
UNION ALL SELECT 'OIL FILTER' ,	'ABC' ,	'RR3' ,	'MM3' ,	'20100113', 200
UNION ALL SELECT 'ENGINE OIL' ,	'ABC' ,	'RR1' ,	'MM1' ,	'20100101' , 150
UNION ALL SELECT 'FUEL FILTER' ,	'XYZ' ,	'RR4' ,	'MM4' ,	'20100101',	50 
UNION ALL SELECT 'OIL FILTER' ,	'XYZ' ,	'RR1' ,	'MM1' ,	'20100101', 200


SELECT
	[description]
	, [repairer]
	, [regno]
	, [make]
	, [pdate]
	, [amount]
FROM
	(
	SELECT
		[description] AS [description]
		, [repairer] AS [repairer]
		, [regno] AS [regno]
		, [make] AS [make]
		, [pdate] AS [pdate]
		, [amount] AS [amount]
		, ROW_NUMBER() OVER (PARTITION BY [description], [repairer] ORDER BY [pdate] DESC) AS [pos]
	FROM
		@rawData
	)
	rep
WHERE
	rep.[pos] = 1

mathie -- ranking on dates like this is sometimes a bad idea because dates are not guaranteed to be unique. in the case of a tie for the date what would you like to determine the order?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 05:45:20

Mathie -- just saw this is a sqlserver 2000 post. ROW_NUMBER() doesn't exist in sqlserver2000.

Can you confirm if you are actually using 2000?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 05:57:29

This might work for 2000


SELECT
	r.[description]
	, r.[repairer]
	, r.[regno]
	, r.[make]
	, r.[pdate]
	, r.[amount]
FROM

	@rawData r

	JOIN (
		SELECT
			[description] AS [description]
			, [repairer] AS [repairer]
			, MAX([pdate]) AS [maxDate]
		FROM
			@rawData
		GROUP BY
			[description]
			, [repairer]
		)
		rep ON
			rep.[description] = r.[description]
			AND rep.[repairer] = r.[repairer]
			AND rep.[maxDate] = r.[pDate]
ORDER BY
	[repairer]
	, [description]

But it will still return more than one row per repairer / description pair if that matches more than once to the most recent date.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mathie
Starting Member

4 Posts

Posted - 2010-02-05 : 10:23:52

Charlie - I use SQL 2000.
in case of tie in the dates, well the it should just pick anyone because it is expected that the Amount for a Description by Repairer should be same for the day.

When I tried the SQL statement you showed below, I got these error messages :

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Repairer'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.

Help will be appreciated

Mathie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 10:28:26

hmm -- works on 2005. This works and gives me results:


DECLARE @rawData TABLE (
	[description] VARCHAR(255)
	, [repairer] CHAR(3)
	, [regno] CHAR(3)
	, [make] CHAR(3)
	, [pdate] DATETIME
	, [amount] MONEY
	)

INSERT @rawData
          SELECT 'OIL FILTER' ,	'ABC' ,	'RR1' ,	'MM1' ,	'20100201', 300
UNION ALL SELECT 'OIL FILTER' ,	'ABC' ,	'RR2' ,	'MM2' ,	'20100101',	200
UNION ALL SELECT 'FUEL FILTER' ,	'ABC' ,	'RR1' ,	'MM1' ,	'20100201',	100
UNION ALL SELECT 'OIL FILTER' ,	'XYZ' ,	'RR1' ,	'MM1' ,	'20100201',	300
UNION ALL SELECT 'FUEL FILTER' ,	'XYZ' ,	'RR1' ,	'MM1' ,	'20100201',	100
UNION ALL SELECT 'ENGINE OIL' ,	'ABC' ,	'RR2',	'MM2' ,	'20100201',	150
UNION ALL SELECT 'OIL FILTER' ,	'ABC' ,	'RR3' ,	'MM3' ,	'20100113', 200
UNION ALL SELECT 'ENGINE OIL' ,	'ABC' ,	'RR1' ,	'MM1' ,	'20100101' , 150
UNION ALL SELECT 'FUEL FILTER' ,	'XYZ' ,	'RR4' ,	'MM4' ,	'20100101',	50 
UNION ALL SELECT 'OIL FILTER' ,	'XYZ' ,	'RR1' ,	'MM1' ,	'20100101', 200


SELECT
	r.[description]
	, r.[repairer]
	, r.[regno]
	, r.[make]
	, r.[pdate]
	, r.[amount]
FROM

	@rawData r

	JOIN (
		SELECT
			[description] AS [description]
			, [repairer] AS [repairer]
			, MAX([pdate]) AS [maxDate]
		FROM
			@rawData
		GROUP BY
			[description]
			, [repairer]
		)
		rep ON
			rep.[description] = r.[description]
			AND rep.[repairer] = r.[repairer]
			AND rep.[maxDate] = r.[pDate]
ORDER BY
	[repairer]
	, [description]

I don't have a 2000 environment lying around any more.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 10:30:01

I imagine the only place to change would be the ORDER BY. everything else uses qualified colummns. Try


ORDER BY
    r.[repairer]
    , r.[description]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mathie
Starting Member

4 Posts

Posted - 2010-02-05 : 10:51:23

Wonderful. It works fine and I very grateful.
Lots of Thanks Charlie

Mathie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources