Post reply

Help on sorting results using sql

  • March 15, 2012 at 7:57 am

    #252630

    Hi,

    I need your help on the following query that I want to build.

    I have a table with the following columns:

    ID, Code, Description

    And I have a user typing in a keyword "keyword" that I need to search into "Description". The requirement is that I bring the results sorted in the following way: First the matching descriptions that start with the keyword given and then the matching descriptions that contain the keyword given. Also, inbetween the two groups, the keywords need to be alphabetically ordered.

    So, for example, if I have the descriptions:

    Aberlin

    Berlin

    Berlina

    Zedberlin

    Youberlin

    Irrelevant

    MoreIrrelevant

    and the keyword is "ber", the query should return:

    Berlin

    Berlina

    Aberlin

    Youberlin

    Zedberlin

    Is there an SQL statement that can help me out here?

    Thanks in advance

    Panayotis

  • March 15, 2012 at 8:26 am

    #1459882

    Please, read the article from the link at the bottom of my signature

    -- to help your helper you should provide

    -- "I have table like that:" like that:

    create table #City (CityName Varchar(50))

    insert #City

    select 'Aberlin' union all

    select 'Berlin' union all

    select 'Berlina' union all

    select 'Zedberlin' union all

    select 'Youberlin' union all

    select 'Irrelevant' union all

    select 'MoreIrrelevant'

    -- here is an idea of how you can do

    -- this sort of thing

    declare @keyword varchar(50)

    set @keyword = 'ber'

    select CityName

    from #City

    where CityName like '%' + @keyword + '%'

    order by (case when CityName like @keyword + '%' then 1 else 2 end)

    ,CityName

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 15, 2012 at 8:26 am

    #1459883

    Hi

    Does do what you need?

    DECLARE @temp AS TABLE

    (City VARCHAR (20))

    INSERT INTO @Temp

    SELECT'Aberlin'

    UNION ALL SELECT'Berlin'

    UNION ALL SELECT'Berlina'

    UNION ALL SELECT'Zedberlin'

    UNION ALL SELECT'Youberlin'

    UNION ALL SELECT'Irrelevant'

    UNION ALL SELECT'MoreIrrelevant'

    SELECT *

    FROM @Temp

    WHERE

    City Like '%ber%'

    ORDER BY

    CHARINDEX('ber',City)

    ,City

    Andy

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • March 15, 2012 at 8:31 am

    #1459892

    Eugene Elutin (3/15/2012)

    Please, read the article from the link at the bottom of my signature

    -- to help your helper you should provide

    -- "I have table like that:" like that:

    create table #City (CityName Varchar(50))

    insert #City

    select 'Aberlin' union all

    select 'Berlin' union all

    select 'Berlina' union all

    select 'Zedberlin' union all

    select 'Youberlin' union all

    select 'Irrelevant' union all

    select 'MoreIrrelevant'

    -- here is an idea of how you can do

    -- this sort of thing

    declare @keyword varchar(50)

    set @keyword = 'ber'

    select CityName

    from #City

    where CityName like '%' + @keyword + '%'

    order by (case when CityName like @keyword + '%' then 1 else 2 end)

    ,CityName

    Looks like you beat me to it Eugene! 😉

    Execution plans look identical on this small subset of data as well..

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • March 15, 2012 at 8:39 am

    #1459902

    Thank you very much. It does what I want.

    However, I do not understand why and how?

    If you have any link that you can direct me to in order for me to learn, I would appreciate.

    Thanks again.

    Panayotis

    P.S. I will read the article and next time I will be quite more correct on how I am posting here.

  • March 15, 2012 at 8:44 am

    #1459907

    Andy, actually, use of CHARINDEX for ORDER BY here will not satisfy OP requirements. Try to add another City like that:

    UNION ALL SELECT'Zberlin'

    You query will place it straight after Aberlin, while OP requirements state that if the word contains a search-word, it should be sorted it alphabetical order...

    Also, using CASE WHEN in ORDER BY, shows how almost any custom order can be achieved in T-SQL.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 15, 2012 at 8:56 am

    #1459921

    However, I do not understand why and how?

    If you have any link that you can direct me to in order for me to learn, I would appreciate.

    Ok.

    Both samples performs search using LIKE with wild-card, filtering out all cases where City name contains search-word at all.

    Then:

    My sample uses CASE WHEN statement to evaluate "sort order business rule" and give it a "priority order". If the CityName start with the search-word (LIKE 'ber%') it gives "priority order" of 1, else (the only option left, the city name just contains a search-word) it gives the "priority order" of 2, then it sort also by city name itself.

    So, as result you have : All cities which name starts with search-word sorted alphabetically, then all cities which name contains the search-word, also sorted alphabetically.

    You can see that using CASE WHEN can be used to enforce any sorting rule, for example lets say that you want to do sort as you have but, if a city name has a dash in it you want to see it first. You can do it by simply adding another condition into CASE WHEN:

    WHEN CHARINDEX('-',CityName) != 0 THEN -1 -- making it to be sorted first!

    The second sample uses CHARINDEX function which returns the position of a search-word within a CityName. It works for your set of sample data, but doesn't satisfy your sorting requirements (see my previous post). As it will sort in the following order:

    Berlin - CHARINDEX('ber',City) will return 1

    Aberlin - CHARINDEX('ber',City) will return 2

    Zberlin - CHARINDEX('ber',City) will also return 2

    Aaberlin - CHARINDEX('ber',City) will return 3

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 15, 2012 at 9:55 am

    #1459973

    Eugene Elutin (3/15/2012)

    Andy, actually, use of CHARINDEX for ORDER BY here will not satisfy OP requirements. Try to add another City like that:

    UNION ALL SELECT'Zberlin'

    You query will place it straight after Aberlin, while OP requirements state that if the word contains a search-word, it should be sorted it alphabetical order...

    Also, using CASE WHEN in ORDER BY, shows how almost any custom order can be achieved in T-SQL.

    Ahh your right Eugene! :blush:

    Simple change to the ordering from this:

    ORDER BY

    CHARINDEX('ber',City)

    ,City

    To This

    City

    ,CHARINDEX('ber',City)

    Should handle this...

    I agree that the CASE statement will allow an element of flexibility however 😀

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • March 15, 2012 at 10:10 am

    #1459981

    Simple change to the ordering from this:

    ORDER BY

    CHARINDEX('ber',City)

    ,City

    To This

    City

    ,CHARINDEX('ber',City)

    Should handle this...

    Nope, it will not.

    It will place Aberlin before Berlin....

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 15, 2012 at 10:14 am

    #1459984

    Eugene Elutin (3/15/2012)

    Simple change to the ordering from this:

    ORDER BY

    CHARINDEX('ber',City)

    ,City

    To This

    City

    ,CHARINDEX('ber',City)

    Should handle this...

    Nope, it will not.

    It will place Aberlin before Berlin....

    I think I need to go home, its obviously been too long a day!! :blink:

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • March 15, 2012 at 10:38 am

    #1459992

    You can use CHARINDEX, if you really want to:

    ORDER BY CAST(NULLIF(CHARINDEX('ber',City), 1) AS BIT)

    ,City

    I'm not sure if you will like it, especially knowing that NULLIF is compiled into CASE WHEN ....

    :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy