Select Distinct syntax problem

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 syntax problem

Author

Topic

apwestgarth
Starting Member

10 Posts

Posted - 2006-09-27 : 06:03:23

Hi I have Two tables of the following structure:

ProductDescription Table -

ProductDescriptionRowID - int - 4 - identity - No Nulls
ProductID - int - 4 - Nulls Allowed
DescriptionPhraseID - nvarcher - 10 - Nulls Allowed

Translations Table

Language - nvarchar - 10 - Nulls Allowed
PhraseKey - nvarchar - 50 - Nulls Allowed
PhraseID - nvarchar - 10 - Nulls Allowed
PhraseTranslation - nvarchar - 4000 - Nulls Allowed

I have a stored procedure:

CREATE PROCEDURE [GetProductDescriptions]
@country nvarchar(3),
@language nvarchar(3),
@productID int
AS
SELECT DISTINCT PD.[ProductDescriptionRowID], PD.[DescriptionPhraseID], T.[PhraseTranslation] FROM [dbo].[ProductDescription] AS PD
INNER JOIN [dbo].[Translations] AS T ON T.[PhraseID] = PD.[DescriptionPhraseID]
WHERE PD.[ProductID] = @productID
AND T.[PhraseKey] = 'InternetDescription'
AND T.[Language] = @language
ORDER BY
T.[PhraseTranslation] ASC
GO

However I would like to be able to get a list of distinct DescriptionPhraseID rather than ProductDescriptionRowID. If I remove the ProductDescriptionRowID from the query I get the result list I want from the database (approx 5 records) but can no longer sort by the ProductDescriptionRowID. If I leave it in I can sort successfully but receive approx 40 results as the distinct is then controlled by the ProductDescriptionRowID and as this is an indentity field it pulls all that match the criteria (We have multiple languages in the tables)

Can anyone advise how I can write this query to have the effect of

SELECT DISTINCT PD.[DescriptionPhraseID], T.[PhraseTranslation] FROM [dbo].[ProductDescription] AS PD
INNER JOIN [dbo].[Translations] AS T ON T.[PhraseID] = PD.[DescriptionPhraseID]
WHERE PD.[ProductID] = 5000300
AND T.[PhraseKey] = 'InternetDescription'
AND T.[Language] = 'ENG'
ORDER BY
T.[PhraseTranslation] ASC

But allow it to be ordered by PD.[ProductDescriptionRowID]?

Thanks

Andrew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 07:17:08

Taken from BOOKS ONLINE

quote:
ORDER BY Clause
Specifies the sort for the result set. The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Syntax
[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ]

Arguments
order_by_expression

Specifies a column on which to sort. A sort column can be specified as a name or column alias (which can be qualified by the table or view name), an expression, or a nonnegative integer representing the position of the name, alias, or expression in select list.

Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.

The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

Peter Larsson
Helsingborg, Sweden

apwestgarth
Starting Member

10 Posts

Posted - 2006-09-27 : 07:23:56

Yes I understand the clause. I have in place the following already but it is not producing the desired output

CREATE PROCEDURE [GetProductDescriptions]
@country nvarchar(3),
@language nvarchar(3),
@productID int
AS
SELECT DISTINCT PD.[ProductDescriptionRowID], PD.[DescriptionPhraseID], T.[PhraseTranslation] FROM [dbo].[ProductDescription] AS PD
INNER JOIN [dbo].[Translations] AS T ON T.[PhraseID] = PD.[DescriptionPhraseID]
WHERE PD.[ProductID] = @productID
AND T.[PhraseKey] = 'InternetDescription'
AND T.[Language] = @language
ORDER BY
PD.[ProductDescriptionRowID] ASC,
T.[PhraseTranslation] ASC
GO

However this returns approx 40 rows when I only want approx 5 - The rows I want from the query come from

CREATE PROCEDURE [GetProductDescriptions]
@country nvarchar(3),
@language nvarchar(3),
@productID int
AS
SELECT DISTINCT PD.[DescriptionPhraseID], T.[PhraseTranslation] FROM [dbo].[ProductDescription] AS PD
INNER JOIN [dbo].[Translations] AS T ON T.[PhraseID] = PD.[DescriptionPhraseID]
WHERE PD.[ProductID] = @productID
AND T.[PhraseKey] = 'InternetDescription'
AND T.[Language] = @language
ORDER BY
T.[PhraseTranslation] ASC
GO

NOTE - not ProductDescriptionRowID is selected. However I need to be able to sort the result of this query by the ProductDescriptionRowID in order to get the ordering of the returned rows correct.

Does anyone know of a way I can do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 07:38:04

Have you tried this?

SELECT		PD.[DescriptionPhraseID],
		T.[PhraseTranslation]
FROM		[dbo].[ProductDescription] AS PD 
INNER JOIN	[dbo].[Translations] AS T ON T.[PhraseID] = PD.[DescriptionPhraseID] 
WHERE		PD.[ProductID] = @productID 
		AND T.[PhraseKey] = 'InternetDescription' 
		AND T.[Language] = @language
GROUP BY	PD.[DescriptionPhraseID],
		T.[PhraseTranslation]
ORDER BY	MIN(PD.[ProductDescriptionRowID])

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-27 : 14:35:39

ORDER BY	MAX(PD.[ProductDescriptionRowID])

You are asking for a sort order, but you havent really stated the critiria. Should it be sorted on the first (lowest) occurence of ProductDescriptionRowID or the last (highest). Had you given some sample data had you maybe even see it your self

-- The Heisenberg uncertainty principle also applies when debugging

apwestgarth
Starting Member

10 Posts

Posted - 2006-09-28 : 05:37:25

HI Thanks for all your help. That seems to have had the desired effect. I have implemented the query however have a problem with how XML Bulk Load is inserting my data - gonna post about that on the Import/Export forum to see if anyone can help - been trying to do something for approx three days and still can't get it right.

Thanks

Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources