SQL SELECT DISTINCT ORDER BY ISSUE

[gus121]

Hi i wish to only display the top 10 distinct 'SearchStr'.
How can i do this as i have to display the 'SearchResultid' in the Select statment to get the OrderBy to function.

This is my current SQL:

SELECT DISTINCT TOP 10 SearchStr, SearchResultID
FROM         SearchResult
WHERE     (SearchBroad = 0) AND (Hits > 5)
ORDER BY SearchResultID Desc

I read somwhere that i should use the ON Clause but it just errors for me:

SELECT DISTINCT TOP 10 [COLOR=red]ON(SearchStr)[/color], SearchResultID
FROM         SearchResult
WHERE     (SearchBroad = 0) AND (Hits > 5)
ORDER BY SearchResultID Desc

-Gus

 

[Juice05]

Try this:

Select Top 10 *

From (Select Distinct(Field)From Table) TableAlias

Order By Field

 

[gus121]

Hi,

No i get an error Invalid columm name SearchResultID?:

SELECT     TOP 10 *
FROM         (SELECT DISTINCT (SearchStr)
                       FROM          SearchResult) SearchResult
WHERE     (SearchBroad = 0) AND (Hits > 5)
ORDER BY SearchResultID

-Gus

 

[jbenson001]

There is no need for a subselect. Your original code should work. You do NOT have to have a column in the Select list in order to sort by it. If it is saying the column is invalide then it does not exist in your table.. possible spelling mistake?

 

[gus121]

Hi see this artical from microsoft

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp9/html

A SELECT … DISTINCT … ORDER BY command will generate an error if the specified ORDER BY field is not in the SELECT field list.

This is my problem.

thanks

-Gus

 

[vongrunt]

SearchResultID is in SELECT field list (original query)... what's the problem then?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

Of course it's not working.....

Your substring only returns the column SearchStr:

SELECT DISTINCT (SearchStr)

Also, just FYI, you should not make your table alias name the same as an actual table name.

This:

FROM (SELECT DISTINCT (SearchStr)
FROM SearchResult) SearchResult

should actually be something like:

FROM (SELECT DISTINCT (SearchStr)
FROM SearchResult) NewSearchResult

-SQLBill



Posting advice: FAQ481-4875

 

[jbenson001]

Use a temp table

SELECT DISTINCT TOP 10 SearchStr
into #temp
FROM         SearchResult
WHERE     (SearchBroad = 0) AND (Hits > 5)

Select *
from #temp t
inner join SearchResult s ON t.SearchString = s.SearchString
ORDER BY SearchResultID Desc

This is providing that SearchStr is Unique.

 

[SQLBill]

The best way (in my opinion) is to have the WHERE and ORDER BY in your subselect (Table Alias). Then just select the TOP 10 from those results, which are already ORDERed.

SELECT TOP 10 DISTINCT MyResults.SearchStr
FROM (SELECT (SearchStr), SearchResultID
        FROM SearchResult
        WHERE (SearchBroad = 0) AND (Hits > 5)
        ORDER BY SearchResultID) MyResults

-SQLBill



Posting advice: FAQ481-4875

 

[SQLBill]

OOPPPS, remove the () from around SearchStr in the subSelect.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

I guess SearchResultID is unique but SearchStr is not. In that case...

Suppose there are five rows with same SearchStr but different ResultID. Which ID you want to use for SELECT and ORDER BY? Any, first, last?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gus121]

Hi thanks for all your help. Yes SearchResultID was unique not searchstr.
I have solved my own issue i tryed SQLBills method but could not get it to work. So i experimented with the Group By clause. This seems to do the job for me.
ie. bring back the last 10 Unique SearchStr records.

SELECT     TOP 10 SearchStr, Max(SearchResultID) AS SearchResultID
FROM         SearchResult
WHERE     (SearchBroad = 0) AND (Hits > 5)
GROUP BY SearchStr
ORDER BY SearchResultID Desc

-Gus