finding lowest value - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

finding lowest value

Author

Topic

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-06 : 13:57:04

hi,

plz i need very urgent

Iam having 5 columns where i want the lowest value among them problem is when there are NULL values i used isnull to make them as '0' so always iam getting the '0' as lowest values...

col1 col2 col3 col4 col5

1 2 3 null null expected is lowest =1 but iam getting '0'..... plz solve this....

thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-06 : 14:48:02

in the isnull statement replace them with the highest value instead of '0'..something like '9999' or something.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 02:20:09

quote:
Originally posted by krish001

hi,

plz i need very urgent

Iam having 5 columns where i want the lowest value among them problem is when there are NULL values i used isnull to make them as '0' so always iam getting the '0' as lowest values...

col1 col2 col3 col4 col5

1 2 3 null null expected is lowest =1 but iam getting '0'..... plz solve this....

thank you

Post the code you used

Madhivanan

Failing to plan is Planning to fail

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-07 : 03:00:17

quote:
Originally posted by vijayisonly

in the isnull statement replace them with the highest value instead of '0'..something like '9999' or something.

Thank you it is working...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-07 : 03:02:56

select min(column) from table where column is not null

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 06:01:43

quote:
Originally posted by krish001

quote:
Originally posted by vijayisonly

in the isnull statement replace them with the highest value instead of '0'..something like '9999' or something.

Thank you it is working...

Did you see my first reply?

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-10-07 : 07:12:37

quote:
Originally posted by madhivanan

quote:
Originally posted by krish001

quote:
Originally posted by vijayisonly

in the isnull statement replace them with the highest value instead of '0'..something like '9999' or something.

Thank you it is working...

Did you see my first reply?

Madhivanan

Failing to plan is Planning to fail

Yes. Yes, I DID see your first post, however, I got the answer hand fed to me, and have no need of you or your questions. Have a nice day!
</sarcasm>

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.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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 09:49:25

quote:
Originally posted by DonAtWork

quote:
Originally posted by madhivanan

quote:
Originally posted by krish001

quote:
Originally posted by vijayisonly

in the isnull statement replace them with the highest value instead of '0'..something like '9999' or something.

Thank you it is working...

Did you see my first reply?

Madhivanan

Failing to plan is Planning to fail

Yes. Yes, I DID see your first post, however, I got the answer hand fed to me, and have no need of you or your questions. Have a nice day!
</sarcasm>

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.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

I wanted to show OP the way without worrying about NULLs

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-07 : 11:39:02

yes 99999999999999999999

What a great answer

That's like Dates having a default where they have no actual data...like in Date of Death.....

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

Add yourself!
http://www.frappr.com/sqlteam

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-08 : 03:47:22

quote:
Originally posted by madhivanan

quote:
Originally posted by krish001

hi,

plz i need very urgent

Iam having 5 columns where i want the lowest value among them problem is when there are NULL values i used isnull to make them as '0' so always iam getting the '0' as lowest values...

col1 col2 col3 col4 col5

1 2 3 null null expected is lowest =1 but iam getting '0'..... plz solve this....

thank you

Post the code you used

Madhivanan

Failing to plan is Planning to fail

i used this code

case
when Min(isnull([Cff Rating 1],9999)) <=Min(isnull([Cff Rating 2],9999)) and Min(isnull([Cff Rating 1],9999)) <=Min(isnull([Cff Rating 3],9999)) AND Min(isnull([Cff Rating 1],9999)) <=Min(isnull([Cff Rating 4],9999)) AND Min(isnull([Cff Rating 1],9999)) <=Min(isnull([Cff Rating 5],9999))
then Min(isnull([Cff Rating 1],9999))

when Min(isnull([Cff Rating 2],9999)) <= Min(isnull([Cff Rating 1],9999)) and Min(isnull([Cff Rating 2],9999)) <= Min(isnull([Cff Rating 3],9999))
AND Min(isnull([Cff Rating 2],9999)) <= Min(isnull([Cff Rating 4],9999)) AND Min(isnull([Cff Rating 2],9999)) <= Min(isnull([Cff Rating 3],9999)) then Min(isnull([Cff Rating 2],9999))

when Min(isnull([Cff Rating 3],9999)) < =Min(isnull([Cff Rating 1],9999)) and Min(isnull([Cff Rating 3],9999)) <= Min(isnull([Cff Rating 2],9999))
AND Min(isnull([Cff Rating 3],9999)) < =Min(isnull([Cff Rating 4],9999)) AND Min(isnull([Cff Rating 3],9999)) < =Min(isnull([Cff Rating 5],9999)) then Min(isnull([Cff Rating 3],9999))

when Min(isnull([Cff Rating 4],9999)) < =Min(isnull([Cff Rating 1],9999)) and Min(isnull([Cff Rating 4],9999)) <= Min(isnull([Cff Rating 2],9999))
AND Min(isnull([Cff Rating 4],9999)) < =Min(isnull([Cff Rating 3],9999)) AND Min(isnull([Cff Rating 4],9999)) < =Min(isnull([Cff Rating 5],9999)) then Min(isnull([Cff Rating 4],9999))

when Min(isnull([Cff Rating 5],9999)) < =Min(isnull([Cff Rating 1],9999)) and Min(isnull([Cff Rating 5],9999)) <= Min(isnull([Cff Rating 2],9999))
AND Min(isnull([Cff Rating 5],9999)) < =Min(isnull([Cff Rating 3],9999)) AND Min(isnull([Cff Rating 5],9999)) < =Min(isnull([Cff Rating 4],9999)) then Min(isnull([Cff Rating 5],9999))
else 0 end as lowest

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 03:59:18

It may be something like this

select unique_col,min(col1) as min_val from
(
select unique_col,col1 from your_table
union all
select unique_col,col2 from your_table
union all
select unique_col,col3 from your_table
union all
select unique_col,col4 from your_table
union all
select unique_col,col5 from your_table
) as t
group by unique_col

Madhivanan

Failing to plan is Planning to fail

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-08 : 04:06:35

quote:
Originally posted by madhivanan

It may be something like this

select unique_col,min(col1) as min_val from
(
select unique_col,col1 from your_table
union all
select unique_col,col2 from your_table
union all
select unique_col,col3 from your_table
union all
select unique_col,col4 from your_table
union all
select unique_col,col5 from your_table
) as t
group by unique_col

Madhivanan

Failing to plan is Planning to fail

wat will be the unique_col

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 04:16:42

quote:
Originally posted by krish001

quote:
Originally posted by madhivanan

It may be something like this

select unique_col,min(col1) as min_val from
(
select unique_col,col1 from your_table
union all
select unique_col,col2 from your_table
union all
select unique_col,col3 from your_table
union all
select unique_col,col4 from your_table
union all
select unique_col,col5 from your_table
) as t
group by unique_col

Madhivanan

Failing to plan is Planning to fail

wat will be the unique_col

The column which is unique in the table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-09 : 01:48:58

quote:
Originally posted by krish001

hi,

plz i need very urgent

Iam having 5 columns where i want the lowest value among them problem is when there are NULL values i used isnull to make them as '0' so always iam getting the '0' as lowest values...

col1 col2 col3 col4 col5

1 2 3 null null expected is lowest =1 but iam getting '0'..... plz solve this....

thank you

What did you change in this post?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources