Using (DISTINCT) an replace as part of the sort

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)

Using (DISTINCT) an replace as part of the sort

Author

Topic

rett
Starting Member

35 Posts

Posted - 2005-07-19 : 15:45:49

I have this sql statement that I am replacing "-" with a space so that the sql will sort the data correctly. My problem that I am having is that I have a "DISTINCT" as part of my select that is causing me a problem.

Can someone help me with this sql statement or give me a different direction to go.

SELECT DISTINCT TOP 50
C.PUB_ID, C.PUB_SECTION_ID, C.DIR_CATEGORY_ID, CD.CATEGORY_DESCRIPTION

FROM dbo.DI_CATEGORIES C INNER JOIN
dbo.DI_CATEGORYDESCS CD ON C.LAST_CAT_LVL_ID = CD.CATEGORY_LVL_ID

ORDER BY replace(CD.CATEGORY_DESCRIPTION,'-',' '), C.CATEGORY_LVL1_ID

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-19 : 15:55:42

so, without the DISTINCT your query runs properly? I mean, other than the duplicate entries the query runs properly?

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-19 : 15:57:20

quote:

My problem that I am having is that I have a "DISTINCT" as part of my select that is causing me a problem.

So are you getting an error? If so, what is the error? If you aren't getting an error, then please describe what the problem is.

Tara

rett
Starting Member

35 Posts

Posted - 2005-07-19 : 17:56:14

Yes with the "DISTINCT" it works correctly.

quote:
Originally posted by eyechart

so, without the DISTINCT your query runs properly? I mean, other than the duplicate entries the query runs properly?

-ec

rett
Starting Member

35 Posts

Posted - 2005-07-19 : 17:56:20

Yes with the "DISTINCT" it works correctly.

quote:
Originally posted by eyechart

so, without the DISTINCT your query runs properly? I mean, other than the duplicate entries the query runs properly?

-ec

rett
Starting Member

35 Posts

Posted - 2005-07-19 : 17:56:25

Yes with the "DISTINCT" it works correctly.

quote:
Originally posted by eyechart

so, without the DISTINCT your query runs properly? I mean, other than the duplicate entries the query runs properly?

-ec

rett
Starting Member

35 Posts

Posted - 2005-07-19 : 17:58:00

The error message that I am getting is the following.

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

quote:
Originally posted by tduggan

quote:

My problem that I am having is that I have a "DISTINCT" as part of my select that is causing me a problem.

So are you getting an error? If so, what is the error? If you aren't getting an error, then please describe what the problem is.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-19 : 18:02:58

You'll need to use a derived table.



SELECT PUB_ID, PUB_SECTION_ID, DIR_CATEGORY_ID, CATEGORY_DESCRIPTION
FROM
(
	SELECT DISTINCT TOP 50
	C.PUB_ID, C.PUB_SECTION_ID, C.DIR_CATEGORY_ID, CD.CATEGORY_DESCRIPTION, CATEGORY_LVL1_ID
	FROM dbo.DI_CATEGORIES C 
	INNER JOIN dbo.DI_CATEGORYDESCS CD 
	ON C.LAST_CAT_LVL_ID = CD.CATEGORY_LVL_ID
) t
ORDER BY replace(CATEGORY_DESCRIPTION,'-',' '), C.CATEGORY_LVL1_ID

Tara

rett
Starting Member

35 Posts

Posted - 2005-07-19 : 20:12:03

Thanks very much tduggan that fixed my problem.

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-19 : 21:56:27

Tara's solution will pick a pseudo-random top 50 and order those.

If you want the TOP 50 of the ORDER BY, you need a slight change...


SELECT TOP 50 PUB_ID, PUB_SECTION_ID, DIR_CATEGORY_ID, CATEGORY_DESCRIPTION
FROM
(
	SELECT DISTINCT 
	C.PUB_ID, C.PUB_SECTION_ID, C.DIR_CATEGORY_ID, CD.CATEGORY_DESCRIPTION, CATEGORY_LVL1_ID
	FROM dbo.DI_CATEGORIES C 
	INNER JOIN dbo.DI_CATEGORYDESCS CD 
	ON C.LAST_CAT_LVL_ID = CD.CATEGORY_LVL_ID
) t
ORDER BY replace(CATEGORY_DESCRIPTION,'-',' '), C.CATEGORY_LVL1_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources