Unique columns in sort by importance result

Dev Shed Forums Sponsor:

December 30th, 2004, 07:39 AM

MrHansen

Registered User

Join Date: Dec 2004

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Unique columns in sort by importance result

I try to search my data and sort the result by importance.

I'm using a MS Access database and my data (table1) looks like this:

Code:

ID   NAME            TEXT
1    Apples          Good red apples
2    Bananas         Fine yellow bananas
3    Yellow apples   Great yellow apples

I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:

Code:

SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*'
UNION
SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*'
ORDER BY searchorder

The output is this:

Code:

ID   NAME            TEXT                  SEARCHORDER
3    Yellow apples   Great yellow apples   1
2    Bananas         Fine yellow bananas   2
3    Yellow apples   Great yellow apples   2

So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.

Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?

Best regards,
Peter from Denmark

December 30th, 2004, 08:29 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Code:

SELECT id,[name],[text]
FROM table1 
WHERE [name] LIKE '*yellow*'
or [text] like '*yellow*'
order by iif([name] LIKE '*yellow*',1,2)

December 30th, 2004, 09:54 AM

MrHansen

Registered User

Join Date: Dec 2004

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Great! Thank you! It works fine!

In fact I have 4 different colums I want to sort by importance, so I have extended your SQL to this:

Code:

SELECT id,[name],[text],[desc],[art] FROM table1 
WHERE [name] LIKE '*yellow*' OR [text] LIKE '*yellow*' OR [desc] LIKE '*yellow*' OR [text] LIKE '*yellow*'
ORDER BY IIF([name] LIKE '*yellow*',1,IIF([text] LIKE '*yellow*',2,IIF([desc] LIKE '*yellow*',3,4)))

Peter from Denmark

Viewing: Dev Shed Forums > Databases > MS SQL Development > Unique columns in sort by importance result

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump