October 5, 2011 at 2:51 pm
Hello, I am stumped on this. I am doing a query for search results. If the user enters 'val', I need to return first everything that starts with VAL then everything that includes VAL.
Here is my query:
select Short_Name from t_One where short_name like 'val%'
union
select short_Name from t_One where short_name like '%val%'
First half normally returns this
Val Pak
Valley Tent Rental
Valley Roofing
Second half returns
Festival of Foods
Hidden Valley
Val Pak
Valley Tent Rental
Valley Roofing
If I do it as a union it sorts in no particular order. If I change it to UNION ALL, it does what I want, however, I have the duplicate issue. Any thoughts? Is this even the best way to give the results? At the end of the day I want to display the above results like this:
Val Pak
Valley Tent Rental
Valley Roofing
Festival of Foods
Hidden Valley
Any help would be appreciated!
John
October 5, 2011 at 2:55 pm
Why are you double scanning?
select Short_Name from t_One where short_name like 'val%'
union
select short_Name from t_One where short_name like '%val%'
Returns the equivalent of:
select short_Name from t_One where short_name like '%val%'
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2011 at 2:55 pm
You can add a case statement as a where clause like this:
select Short_Name from t_One where short_name like 'val%'
union
select short_Name from t_One where short_name like '%val%'
order by case when short_name like 'val%' then 0 else 1 end, short_name
This will order by val% first and then %val%. It has added advantage that it orders each logical group by short_name. This way val1, val2, 2val1, 2val2 will be returned in that order.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2011 at 2:56 pm
Good point Kraig. Just use his query with my order by clause and you should be there. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2011 at 3:04 pm
The result of the query is going to a List Box on a VB.net form (for what its worth). The preferred result will be everything that starts with VAL (VAL pak, VALue Carpet, VALley Tent), THEN display the items that include VAL in asc order (Dynasty VALet, Hidden VALley, Sudden VALues).
So if I do just the %val% in my query, I will get them in this order:
Dynasty VALey
Hidden VALey
Sudden VALues
VAL Pak
VALue Carpet
VALley Tent
Does this add clarity?
October 5, 2011 at 3:17 pm
Sean, thanks for reading completely through my question to give your answer which was the solution. My original thought with the union was to join two result sets. I didn't know about using a Case in the ORDER BY.
Thanks to all!
October 5, 2011 at 3:19 pm
Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2011 at 7:41 pm
john.clyma 97764 (10/5/2011)
Sean, thanks for reading completely through my question to give your answer which was the solution. My original thought with the union was to join two result sets. I didn't know about using a Case in the ORDER BY.Thanks to all!
You did say it, I just... missed it. Apologies John. I got stuck in the functionality instead of the intended result.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply