SQL Sorting

Thread View: borland.public.intrabuilder.data.sql

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by DC Tue, 09 Jun 1998 00:00

#74

Author: DC
Date: Tue, 09 Jun 1998 00:00

19 lines
1214 bytes

If you have a table which contains info on people. For Example:

PersonTable (Table Name)
Name, Hair_Color and  Eye_Color (Fields)

How would I write a SELECT query to search for people whos name is "TED" has
"BROWN" hair and "GREEN" eyes? I want to return records sorted by bestmatch.
ie. Records that contain all three of the search criteria, then records that
contain "TED" and "BROWN" hair, then records that contain "TED"
and "GREEN" eyes, and so on.

AND searches would simply omit records that didn't contain all three and an
OR search would return all records with any of the criteria, but not in
order of relevance.
I asked this question once before here, but no-one had a detail answer. I
know what needs to be done, just not how to word it in SQL.

-Derek

Re: SQL Sorting

#75

Author: Henning Hirschmann
Date: Mon, 22 Jun 1998 00:00

19 lines
997 bytes

Hi,

it is a bit tricky, but this should work.

You have the three parameters for name, hairs and eyes, yea? Then say

"Select iif(eyes=param_eyes and name = param_name and hairs = param_hairs ,
3, iif(eyes = eyes_param and name = name_param, 2, iif(eyes = eyes_param, 1,
0))) AS HITS, * from ..table where eyes = Param_eyes or hairs = param_hairs
or name = param_name order by HITS DESC"

This iif does nothing but count the matches per record.

Hope it works ,-))

Greetings

Henning

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads