sql query order output

Question

I have table called Property and the data is like

   pid     city           state          state_abb      address1   address2
    x1     NewCity        NHANy              NH          xxxx      gfg
    x2     Gloucester     Manchestar         MA          newAde    xxxx
    x3     OtherC         NewYork            NY          yyyy

I want a query with search keyword to display order like (state or state_abb), city, address1, adress2

For ex:

If I search keyword with New The result output should

    pid   city         state        state_abb   address1    address2
     x3   OtherC       NewYork         NY       yyyy 
     x1   NewCity      NHANy           NH       xxxx        gfg
     x2   Gloucester   Manchester      MA       newAde      xxxx

I don't want unmatched rows.I want display only matched rows.

Thanks in advance

What did you try? Please post the queries you attempted and explain where you are having problems. — Oded
– Oded, Commented Sep 28, 2011 at 20:13
@Oded Agreed, I don't want to type the whole query, but am willing to help... — D'Arcy Rittich
– D'Arcy Rittich, Commented Sep 28, 2011 at 20:15
Thank you for your reply i am poor in sql so plz Give me some idea i can try — Sree
– Sree, Commented Sep 28, 2011 at 20:18

Sparky · Accepted Answer · 2011-09-28 21:44:13Z

3

It think the question is how to order the results, not how to perform the search. Try this as your ORDER BY clause

order by
 case when charindex('NEW',state) >0 then 1000 else 0 end desc,
 case when charindex('NEW',city) >0 then 100 else 0 end desc, 
 case when charindex('NEW',address) >0 then 10 else 0 end desc

Where clause to only get matching rows

 select * from Property where
        city     like '%New%' or
        state    like '%New%' or
        address1 like '%New%' or
        address2 like '%New%'

edited Sep 28, 2011 at 21:44

answered Sep 28, 2011 at 20:35

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sree Over a year ago

Can u tell me the query i dont want unmatched data rows

Sparky Over a year ago

If you don't want unmatched rows, you need a where clause like Jim Garrisson suggested below. I'll add it to my answer above.

Taryn · Accepted Answer · 2011-09-28 20:27:26Z

0

You could try something like this:

SELECT pid, city, state, stat_abb, address1, address2
from property
where city like '%New%'
OR state like '%New%'
OR address1 like '%New%'

answered Sep 28, 2011 at 20:27

Taryn

249k57 gold badges375 silver badges409 bronze badges

2 Comments

marc_s Over a year ago

of course, searching on several fields with OR and also using LIKE with a leading % will completely and utterly avoid any possible indices to be used, and thus performance will be horribly slow....

Taryn Over a year ago

@marc_s agreed but with the info he provided it gives him a startng point

Jim Garrison · Accepted Answer · 2011-09-28 20:27:47Z

0

This should get you started:

select * from Property where
    city     like '%New%' or
    state    like '%New%' or
    address1 like '%New%' or
    address2 like '%New%'

I assume you don't want to look at the pid column.

answered Sep 28, 2011 at 20:27

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

5 Comments

marc_s Over a year ago

of course, searching on several fields with OR and also using LIKE with a leading % will completely and utterly avoid any possible indices to be used, and thus performance will be horribly slow....

Jim Garrison Over a year ago

@marc_s agreed, but the OP hasn't given much context. This could be perfectly usable if the table size isn't large

marc_s Over a year ago

indeed - just wanted to point out that this approach is not be the best to scale to large numbers of rows .... for small numbers, it's irrelevant

Jim Garrison Over a year ago

"Its not working" is not enough information. What do you mean?

Sree Over a year ago

Its giving same table output not getting order

Mikael Eriksson · Accepted Answer · 2011-09-28 20:37:12Z

0

select *
from Property 
order by case
           when state     like 'New%' then 1
           when state_abb like 'New%' then 1
           when city      like 'New%' then 2
           when address1  like 'New%' then 3
           when address2  like 'New%' then 4
         end

answered Sep 28, 2011 at 20:37

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

sql query order output

4 Answers 4

2 Comments

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related