Post reply

Near Number Function

  • August 11, 2005 at 3:03 pm

    #89498

    Hi,

    I have a table called EmpAges with 2 fields: Name & Age. I want to know if there is a way to get all the employees near an age. For example if my value is 20 then I want all the people near the age of 20 first then the people age 19, then 20, then 18, then 22 and so on.

    Does SQL Server have a function that would allow such a query?

    Thanks,

    Bob

  • August 11, 2005 at 3:17 pm

    #581598

    First you should be keeping the date of birth instead of the age.

    2nd :

    Select whatever from dbo.Employees where ABS(@WantedAge - Age) = @MaxAgeDifference

    order by ABS(@WantedAge - Age), Age, Name

  • August 11, 2005 at 3:18 pm

    #581599

    It seems like what you are looking for is a fuzzy search on top of an order by that is not supported.

    you can try something like:

    insert into #EmpAges (Name, Age)

    select 'Abraham', 18 union

    select 'Reginald', 17 union

    select 'Cheryl', 16 union

    select 'Michel', 19 union

    select 'Innes', 20 union

    select 'Ann', 21 union

    select 'Marjorie', 22 union

    select 'Morningstar', 23 union

    select 'Burt', 24 union

    select 'Sheryl', 19 union

    select 'Livia', 18 union

    select 'Charlene', 17 union

    select 'Stearns', 16 union

    select 'Heather', 19 union

    select 'Michael', 20 union

    select 'Sylvia', 21 union

    select 'Albert', 22 union

    select 'Anne', 23 union

    select 'Meander', 24 union

    select 'Dean', 19 union

    select 'Dirk', 18 union

    select 'Johnson', 20 union

    select 'Akiko', 21

    Declare @Age smallint

    Declare @Diff tinyint

    set @Age = 20

    set @Diff = 2

    select *

    from #EmpAges

    where age between (@age - @Diff) and (@age + @Diff)

    But I don't see a way to order it the way you want

  • August 11, 2005 at 3:22 pm

    #581600

    Yeah I still type fast, don't mention it . Not sure that this is exactly what's needed but I guess we'll know soon.

  • August 11, 2005 at 3:23 pm

    #581601

    But I don't see a way to order it the way you want

    select *

    from #EmpAges

    where age between (@age - @Diff) and (@age + @Diff)

    order by abs(age - @age) , age


    * Noel

  • August 11, 2005 at 4:09 pm

    #581612

    The ABS Function did the trick. Thanks Guys!!!

  • August 11, 2005 at 5:05 pm

    #581624

    HTH.

  • August 11, 2005 at 5:08 pm

    #581627

    So you agree with me now. Just wondering if the (age - @Diff) and (age + @Diff) version is sargable (since you both used it).

Viewing 8 posts - 1 through 7 (of 7 total)

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