search and return sorted by two fields 
Author Message
 search and return sorted by two fields

I am looking for some code that will search a database (via sql) and return
the results sorted in the following fashion:

Exact name matches first followed by matches with the highest points score.

For example if I were to search for acme I would get

ACME
ACME
ACME
ACME
ACME industrial supplies
ACME bedding
ACME Coyote supplies
ACME advanced engineering

As you can see the first few results exactly match the search string (as
they would if sorting by name) while the rest are ranked by point score (a
value assigned arbitrarily and having nothing to do with the company
itself), these are not in alphabetical order.

My initial idea was to try to perform a sort on point score and then bump
the exact name matches to the top of the results but I am hoping there is a
more elegant way.

Adam



Sun, 03 Nov 2002 03:00:00 GMT  
 search and return sorted by two fields
For Microsoft Access Database using Table called tblNames, two Fields -
Name, Score:

SELECT tblNames.Name FROM tblNames ORDER BY
IIf(StrComp("ACME",[Name],1)=0,0,[Score])

Note that the value 1 after [Name] in the StrComp function denotes a textual
comaprison, i.e. ignore upper/lowercase. To force an upper/lowercase
comparison, use 0. Also, you will need to replace the "ACME" value with an
input parameter, I've left that bit for yourself  :-)

Martin Harran
Donegal
Ireland


Quote:
> I am looking for some code that will search a database (via sql) and
return
> the results sorted in the following fashion:

> Exact name matches first followed by matches with the highest points
score.

> For example if I were to search for acme I would get

> ACME
> ACME
> ACME
> ACME
> ACME industrial supplies
> ACME bedding
> ACME Coyote supplies
> ACME advanced engineering

> As you can see the first few results exactly match the search string (as
> they would if sorting by name) while the rest are ranked by point score (a
> value assigned arbitrarily and having nothing to do with the company
> itself), these are not in alphabetical order.

> My initial idea was to try to perform a sort on point score and then bump
> the exact name matches to the top of the results but I am hoping there is
a
> more elegant way.

> Adam



Wed, 13 Nov 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. search through two text files and return appropriate value

2. Sort question on two fields

3. Sorted Order of two Fields

4. newbie sort two fields in database

5. Sorting an array on two fields

6. sorting by two fields

7. ADO SQL Search That Returns 0 Records When it Should Return 1

8. Tab to search field on search engines

9. Having trouble accessing single field key with two concatenated fields

10. Fields, how to search for a specific type of field

11. Select distinct on 1 field but returns multiple fields

12. Sorting with two parameters?

 

 
Powered by phpBB® Forum Software