• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1239
  • Last Modified:

Floor and Ceil function

How to do this query in field like this:

Age
-----
6
17
29
33
46
50
76
81
90

When I enter parameter n=48, it should return:

Age
-----
46
50

where 46 is biggest integer smaller than or equal to n, and 50 is smallest integer greater than or equal to n

Thanks
0
lysenko
Asked:
lysenko
  • 2
3 Solutions
 
pivarCommented:
Hi,

How about this:

select t1.Age from table t1 where t1.Age = (select max(t2.Age) from table t2 where t2.Age<@n) or t1.Age = (select min(t3.Age) from table t3 where t3.Age>@n) order by t1.Age

/peter
0
 
Kevin CrossChief Technology OfficerCommented:
Try like this:


/* section used for testing purposes */
DECLARE @tbl TABLE(Age INT)
INSERT INTO @tbl
SELECT 6 
UNION SELECT 17
UNION SELECT 29
UNION SELECT 33
UNION SELECT 46
UNION SELECT 50
UNION SELECT 76
UNION SELECT 81
UNION SELECT 90
 
DECLARE @n INT
SET @n = 48
/* end of testing setup */
 
-- using union
select max(age) As Age from @tbl where age < @n
union all
select min(age) As Age from @tbl where age > @n;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
And I see in the tags you have Access, so my example which is based on SQL Server 2005 may not work but the concept of the query can be done in MS Access like this:


select max(age) As Age 
from your_table_name 
where age < [n]
union
select min(age) 
from your_table_name 
where age > [n];

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now