CODEWITHSUNDEEP
sql
Itay.B
Itay.B

Reputation: 4111

SQL select near by values

I have a table "T_Person" with 2 fields that I would like to search by: Height int, BirthDate datetime.

When I query this table I want to get rows with values that are near what I searched. For example when I search for people with height of 175 cm, like this:

Select * 
from T_Person 
where Height = 175

I would like to also get rows that the height value is near 175. eg 174, 173,176...

Same thing about the date column. When I search for '2003-06-25', I would like to also get dates that are close to it.

Is it possible?

Upvotes: 4

Views: 3171

Answers (5)

MtwStark
MtwStark

Reputation: 4058

first define your bounds, what near means for you:

declare 
@h1 int = -2, -- height diff lower bound (cm)
@h2 int = 1, -- height diff upper bound (cm)
@d1 int = -100, -- birthdate diff lower bound (days)
@d2 int = 30,  -- birthdate diff upper bound (days)

next set what you are looking for:

declare 
@hr int = 181,  -- reference height (cm)
@dr date = '1971-09-24'  -- referencde birthdate

last extract your records

select * 
from (
    select t.*, (t.Height - @hr) diff_cm, DATEDIFF(DAY, @dr, t.BirthDate) diff_days
    from T_Person t
    where t.Height between @hr+@h1 and @hr+@h2
    and DATEDIFF(DAY, @dr, t.BirthDate) between @d1 and @d2
) t

final choose your ranking order as you need..

order by abs(diff_cm) -- only height
order by abs(diff_days) -- only birthdate
order by abs(diff_cm)*30 + abs(diff_days) -- a mix of both..

Hope this help

Upvotes: 0

Chris
Chris

Reputation: 8109

In order to get qualified results, you need to define a scoring function. It can be as simple as sum the difference like this:

select * from t_person  
order by abs(height - 175) + abs(datediff(date, '2003-06-25')) asc

This query will give you all persons ordered by the difference of height and date diff in days.

Upvotes: 0

Ormoz
Ormoz

Reputation: 3013

change the filter:

 Select * from T_Person where Height >= 173 and Height <= 177

Or alternatively:

  Select * from T_Person where Height between 173 and 177

For datetime fields, you should use cast() function to get the best results:

  Select * from T_Person where BirthDate  
     between CAST('2014-02-28' AS DATETIME) and CAST('2015-02-28' AS DATETIME);

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93724

Replace 175 with the value you need. Use this

Select * from T_Person where Height between 175 - 2 and 175 + 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You need some sort of measure on what constitutes close. I also suspect that you want the exact matches to appear first. So, to order the rows by "nearness" use:

select p.*
from t_person p
order by abs(height - 175);

Filtering the results is useful, so you can also add a where clause:

select p.*
from t_person p
where height between 175 - 2 and 175 + 2
order by abs(height - 175);

The same idea can work for dates. However, you don't specify your database in the question and date functions are highly database specific.

Upvotes: 3

Related Questions