Query for selecting (0~50,51~100,101~150...)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query for selecting (0~50,51~100,101~150...)

Author

Topic

yukiever
Starting Member

13 Posts

Posted - 2011-03-01 : 13:20:27

Hi,
This seems hard for me....

I have a table with column 'number'
In that column I have 50,100,150,200,250,300 values.

When a user choose a number 29, I want the row with '50' to return.
When a user choose a number 99, I want the row with '100' to return.
When a user choose a number 260, I want the row with '300' t return.

So basically I want the floor of the number, increasing by 50.

I tried different way, but too hard for me T.T

SELECT * From test_table28 WHERE number BETWEEN GOT STUCK HERE

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-01 : 13:49:19

Try this:

--Sample Data
Create table #test
(
TestVal int
)

Insert into #test
Select 50 union
Select 100 union
Select 150 union
Select 200 union
Select 250

--Code to get the result.

Declare @SearchVal int
set @SearchVal = 29
select * from #test where TestVal = (convert(int, @SearchVal +50)/50)*50

Regards,
Bohra

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-01 : 14:18:35

Check this out

select top 1 col1 from tableName where Col1>=@value order by col1

As per your inputs use Only > (greater than) sign in order to get the required results. But what if you provide input as 50 , still it should return 100? if so it would be fine to use > sign, but if you need to return 50 as a result of input=50 then use >= sign

Cheers
MIK

yukiever
Starting Member

13 Posts

Posted - 2011-03-03 : 16:47:07

quote:
Originally posted by MIK_2008

Check this out

select top 1 col1 from tableName where Col1>=@value order by col1

As per your inputs use Only > (greater than) sign in order to get the required results. But what if you provide input as 50 , still it should return 100? if so it would be fine to use > sign, but if you need to return 50 as a result of input=50 then use >= sign

Cheers
MIK

Thanks It works~!

yukiever
Starting Member

13 Posts

Posted - 2011-03-03 : 16:47:39

quote:
Originally posted by pk_bohra

Try this:

--Sample Data
Create table #test
(
TestVal int
)

Insert into #test
Select 50 union
Select 100 union
Select 150 union
Select 200 union
Select 250

--Code to get the result.

Declare @SearchVal int
set @SearchVal = 29
select * from #test where TestVal = (convert(int, @SearchVal +50)/50)*50

Regards,
Bohra

Thanks for the reply!
I got it working

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-03 : 17:56:27

This seems hard for me...I want anyone who dabbles in database work to understand database normalization before they get hurt

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-04 : 10:15:10

Not happening Bret. Ever. EVAR.

Now a tequila DB... possible

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources