| Author |
Topic |
|
annabanana
Starting Member
10 Posts |
 Posted - 2010-01-26 : 10:14:38
|
| Hello, For my company, i need to fill a gridview with all the customers (names, dates of arrival, email) and the user has the possibility to search for a name.When the user searches for a name by typing it in the field, the results give all the names, even those which contains internally the letters. For example, if you type : ama...you'll have : amar, amari, amalet but also mamal, loama...all the names which contains those three letters !that's great but the names which result from the search should be first ordered by date (date of arrival as customer), by name (but the first letter the user type should appear first).for example you should have if you type : "mir"miranamirelmirenomirowlamiramiredbemirochemiryou see, the m should appear first because the user typed m in the search field !if someone has a solution ...i tried an ORDER BY which contains @custom_field as a condition but sql will accept only column names... |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 10:20:57
|
| [code]ORDER BY CASE WHEN NameColumn LIKE 'mir' + '%' THEN 1 ELSE 2 END, NameColumn[/code] |
 |
|
|
annabanana
Starting Member
10 Posts |
Posted - 2010-01-26 : 11:20:57
|
| ok, I wrote : select.... from ... order by date desc, case when NAME like '@custom_field' + '%' then 1 ELSE 2 END, NAME asc";but it says : "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."and yes there is a distinct in my selection but without the case when it was ok but not doing what i want of course... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 11:28:55
|
You needselect.... from ... order by date desc, case when NAME like @custom_field + '%' then 1 ELSE 2 END, NAME asc i.e. without the quotes around @custom_field (otherwise it will compare NAME column's value against the fixed string "@custom_field%").The fields in your ORDER BY must occur in your SELECT if you use Distinct. You will have to add any that are missing. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 11:36:24
|
P.S. I presume that the @custom_field parameter contains "MIR" (in the previous example)? Its just that the name of that variable implies that it contains the name of a column, rather than a data-value, and I wanted to be sure  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-26 : 11:36:34
|
quote:
Originally posted by annabanana
ok, I wrote : select.... from ... order by date desc, case when NAME like '@custom_field' + '%' then 1 ELSE 2 END, NAME asc";but it says : "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."and yes there is a distinct in my selection but without the case when it was ok but not doing what i want of course...
if you're using distinct then as suggested by error you need to have all items used in order by in the select list |
|
|
|
annabanana
Starting Member
10 Posts |
Posted - 2010-01-26 : 12:17:00
|
quote:
Originally posted by Kristen
P.S. I presume that the @custom_field parameter contains "MIR" (in the previous example)? Its just that the name of that variable implies that it contains the name of a column, rather than a data-value, and I wanted to be sure
that's all my problem... the request must order the first results by the letters the user typed !! so it's a data-value and not the name of a column... 'mir' is typed in a field and changes everytime a user launches a search by name... so i cant put in the selection @custom_field because it's not in the database... and my "order by date desc, case when NAME like @custom_field + '%' then 1 ELSE 2 END, NAME asc " won't work because @custom_field is a data value extracted from a field in the interface... but I need the results to be ordered by the letters the user typed first then by name... so i need to indicate to sql what are those letters ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 12:21:50
|
| OK, presumably you had "MIR" in some parameter - in order to be able to find records containing "MIR" ?Whatever that "parameter" is that is what you need to put in the ORDER BY. |
|
|
|
annabanana
Starting Member
10 Posts |
Posted - 2010-01-26 : 14:29:08
|
quote:
Originally posted by Kristen
OK, presumably you had "MIR" in some parameter - in order to be able to find records containing "MIR" ?Whatever that "parameter" is that is what you need to put in the ORDER BY.
the problem is I dont have the string "mir" directly in the code...but I have a textbox in the interface, filled by the user...this is the text in this textbox that will constitute the parameter for the order by... and now, I don't know the syntax to write it in the request.. I'll try to put simply the parameter's name without any @, nor quotes, but there are chances that it's gonna tell me to put what's in the order by in the selection (the thing is my parameter isn't the name of a column...)but thank you for the else then end, I think that's the way the query should be and the idea wouldn't have come to me... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 14:45:42
|
Presumably you have the value "MIR" stored in something in order to put it in a LIKE in the WHERE clause?If its just a dynamic SQL string that you are making (e.g. in your application), and executing, you don't need to put "MIR" in a parameter, just incorporate it in the ORDER BY clause as a string:order by ... case when NAME like 'MIR%' then 1 ELSE 2 END ... I can't be certain, but if you put it in the CASE statement in the ORDER BY, and if it is a constant (which the string "MIR" would be) then I don't think it will need to be in the SELECT. |
|
|
|
annabanana
Starting Member
10 Posts |
Posted - 2010-01-26 : 15:02:47
|
| ok so if my parameter is called "custom_field"I write : order by ... case when NAME like 'custom_field%' then 1 ELSE 2 END ...'mir' was just an example, it could be anything typed by the user...that's why I need to put the name of the parameter ... but will sql understand that's it's not a value but a parameter name...I'm not sure, that's why I wanted to put a @ before the parameter's name in the query.. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 16:33:35
|
| [code]order by ... case when NAME like 'custom_field%' then 1 ELSE 2 END ...[/code]You need to understand the difference between when you are using a variable, and when its just a string. This is just a string, so NAME will (probably) never match LIKE 'custom_field%'This may help clarify [code]DECLARE @custom_field varchar(10)SELECT @custom_field = 'MIR'SELECT 'custom_field' AS [Test1]SELECT @custom_field AS [Test2][/code]What is your code that finds the values in the first place? (I'm still using "MIR" as an example, but hopefully you understand my meaning) you will have some code that says something like "WHERE NAME LIKE '%MIR%'.1) Is "MIR" in a SQL @Parameter variable? or:2) Is the WHERE clause made from dynamic SQL in your Application language? - e.g. something like:[code]"WHERE NAME LIKE '%" & SomeVariable & "%'"[/code] |
|
|
|
annabanana
Starting Member
10 Posts |
Posted - 2010-01-28 : 05:04:37
|
quote:
Originally posted by Kristen1) Is "MIR" in a SQL @Parameter variable? or:2) Is the WHERE clause made from dynamic SQL in your Application language? - e.g. something like:"WHERE NAME LIKE '%" & SomeVariable & "%'" yes I have this : "WHERE NAME LIKE '%" & SomeVariable & "%'"
Eventually, I found a way to work it out thanks to your help. I explained it briefly, if ever someone has the same need... (never know !)I put in the SELECT :"...case when NAME like "+cf+" THEN 1 ELSE 2 END as ORDER..." , and this parameter, cf, had for value the string custom_field + "%" (it's al little more complicated than that because I have functions that create a parameter by giving it a name, a value...)But I let the where name like '%" + custom_field + "%' and in the order by : "order by ORDER asc, NAME asc"this way, it's 1 first that appear then 2, and as 1 constitutes all the names which begin with the letters the user typed, and 2 all the names that contains those letters, it did just what I needed to. But I finally needed two parameters SQL : the "custom_field +%"and the %+custom_field+%thanks and sorry for my bad english  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 07:22:43
|
| Well done! English is my only language and I find it hard enough! Glad you got it going in the end. |
|
|
|
|
Order by _special condition