SQL query

Order by

[Alternative Future]

Hi guys,

Wondering if anyone can help me out with a sql query?

My query is:
SELECT * from TABLE WHERE COLUMN1 LIKE '%blue%' OR COLUMN1 LIKE '%widgets%' ORDER BY string_value('blue')

I am aware the red part of the query is not correct, but this is what I am after, I want the result to be displayed in order of the first part of my query string. Can we either ORDER or SORT by a given keyword in the query?

Many thanks for all help.

-George

[Alternative Future]

Tried this query out:

SELECT * from TABLE WHERE COLUMN1 LIKE '%blue%' OR COLUMN1 LIKE '%widgets%' ORDER BY ='blue'

But it doesn’t seem to do what I am after... Perhaps the above statement makes no sense can someone point me in the right direction? blue is the first part of the query string which is spilt into blocks using whitespace as the separator, so the ORDER BY will always be the same as the first part of the query COLUMN1 LIKE '%blue%'

Once again thanks,

-George

[sun818]

ORDER BY is for sorting the data in a field. If you provide us with some example results, perhaps we could point you in the right direction...

[Alternative Future]

Hi sun818,

What I am trying to achieve is:

SQL TABLE widgets
-------------------
¦ Name ¦ ID ¦
-------------------
¦blue widgets¦ 1 ¦
¦blue wids ¦ 2 ¦
¦pink widgets¦ 3 ¦

In my search I have a free entry text box to search for widgets, if the user searches for 'blue widgets' I am splitting the string into two parts where I check for all results containing firstly blue then widgets using the %LIKE& for each string value I then want to ORDER BY the first string in their query. So the example shown would ORDER BY blue, as this was the first word in their query.

Hope this makes sense, it’s quite hard to explain when converting the scenario into a non-specific category.

-George

[Alternative Future]

Sorry perhaps I was not clear enough in the last post, so here is another attempt:

SELECT * from WIDGETS WHERE NAME LIKE '%blue%' OR NAME LIKE '%widgets%' ORDER BY?

I want my sql query to get all from my table widgets where NAME LIKE string_value OR NAME LIKE string_value2 I want this to be displayed in order of the first string_value.

i.e. If user searches 'blue widgets' I spilt the string into an array containing two strings where I query the first string then the second and show the results in order of the first query. Another example would be a user searches 'richmond hotel' my result would return all matches to richmond then hotel therefore my results might look like richmond hotel, richmond guest house, seaview hotel, anchor hotel etc.

-George

[sun818]

What if you broke down your query into multiple select queries? I think a UNION QUERY may be of help to you.

[Alternative Future]

That's what I have done is broken the query into an array of query strings,

Will have a look at UNION QUERY tomorrrow.

Thanks for your help.

-George

[Mike_Levin]

SELECT *, mySort = 0 from WIDGETS WHERE NAME LIKE '%blue%'
UNION
SELECT *, mySort = 1 from WIDGETS WHERE NAME LIKE '%widgets%'
ORDER BY mySort

[sun818]

Hi George - I could have written out the query myself, but its good to try yourself before having the answer. :)

[Alternative Future]

Thanks to you both, your help was really appreciated.

Now my query string is:
(SELECT *, NAME = 0 from WIDGETS WHERE NAME LIKE '%blue%' ORDER BY NAME) UNION (SELECT *, NAME = 1 from WIDGETS WHERE NAME LIKE '%widgets%' ORDER BY NAME) ORDER BY NAME

There was a moment I was getting nowhere, then realised my method calling on the SQL randomised the results before returning the object containing the results. This is a feature on another part of the site. ;-)

Once again thanks,

-George