Search between range

Transact-SQL
1

I have a table with 3 columns. If I get lets say the age 35.4 I need to return AgeGroup 2.

I don't know for the life of me why i can't do it.

Please help.

Thanks

CREATE TABLE [dbo].[AgeGroup](
[AgeGroup] [tinyint] NOT NULL,
[FromAgeInYear] [tinyint] NULL,
[ToAgeInYear] [tinyint] NULL,

CONSTRAINT [PK_AgeGroup] PRIMARY KEY CLUSTERED
(
[AgeGroup] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[AgeGroup] ([AgeGroup], [FromAgeInYear], [ToAgeInYear] ) VALUES (1, 0, 35 )
INSERT [dbo].[GAgeGroup] ([AgeGroup], [FromAgeInYear], [ToAgeInYear] ) VALUES (2, 35, 55 )
INSERT [dbo].[AgeGroup] ([AgeGroup], [FromAgeInYear], [ToAgeInYear] ) VALUES (3, 55, 120 )

2

I believe it is simply:

SELECT * FROM AgeGroup WHERE @myage BETWEEN FromAgeInYear AND ToAgeInYear;

The only concern I have is that FromAgeInYear and ToAgeInYear are integers which MAY cause problem.

1 Like
3

This query also will give you the resule in same range

SELECT AgeGroup FROM AgeGroup WHERE FromAgeInYear <=@myage AND ToAgeInYear>=@myage

4

djj55 answer is correct. After posting my post I suddenly realised how to do it and came up with what djj55 did :smile:
Thanks all