Can I select the min value without using cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can I select the min value without using cursor?

Author

Topic

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 00:01:52

Can I select the min value without using cursor?

Table

Cat, value
A, 1
B, 1
A, 0
C, 3
B, 2

Expect output
Cat, value
A, 0
B, 1
C, 3

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-05-19 : 00:04:43

select Cat, min(value)
from YourTable
group by Cat;

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 00:06:21

Thank.

quote:
Originally posted by nathans

select Cat, min(value)
from YourTable
group by Cat;

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-05-19 : 00:09:25

no problem

Nathan Skerl

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 00:12:55

Hi nathans,

Can I insert the select statement in a "case when"?

Because I want the output as following

Cat, Value, Count
A, 1, 0
B, 1, 1
A, 0, 1
C, 3, 1
B, 2, 0

Thank

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-19 : 00:46:44

Hey What is count in above output?

In Love... With Me!

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 00:50:15

If it is min, then the new column count should be display 1

else 0

Thank

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-19 : 01:04:52

Try this

Create Table #t (Cat nvarchar(2),Value int)
Insert into #t
Select 'A', 1 union all
Select 'B', 1 union all
Select 'A', 0 union all
Select 'C', 3 Union all
Select 'B', 2

With Cte as(
Select Cat,value,row=row_number() over (partition by Cat order by value) From #t)
Select Cat,Value,Case When row=1 then 1 else 0 end as count from Cte order by Count

In Love... With Me!

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 01:54:21

Hi raghuveer125,

Thank you very much.

I will try it, but I need more time.

Thank

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 04:36:50

Hi raghuveer125,

Actually, I am using Sybase ase 1.5 which is not support "partition by ".

Do you have any suggestion to solve it in other way?

Thank very much

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-19 : 05:16:41

This is a MS SQL forum.
You can get better help in a Sybase forum.

No, you're never too old to Yak'n'Roll if you're too young to die.

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-05-19 : 12:14:16

Sorry man, didnt see you reply last night. Follow webfred, go to http://www.dbforums.com/sybase/ for help with sybase.

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 21:21:49

ok, thank

man889
Starting Member

25 Posts

Posted - 2011-05-19 : 22:44:05

Hi nathans,

Actually, you and raghuveer125 already gave me the idea how to solve it.

I use this

select Cat, min(value) as min_value into #tmp
from YourTable
group by Cat;

to create a temp table as min value table

then I use a case when to check it whether in the table
case
when (
exists (
select * from #tmp
where YourTable.Cat= #tmp.Cat
and YourTable.value = #tmp.min_value
)
then 1
else 0
end as count_value

I am think if there is a better way to solve it, do you have any idea?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-20 : 02:07:47

Follow webfred, go to http://www.dbforums.com/sybase/ for help with sybase.

But this is last try if not work then go for http://www.dbforums.com/sybase/ and post your query

Create Table #t (Cat nvarchar(2),Value int)
Insert into #t
Select 'A', 1 union all
Select 'B', 1 union all
Select 'A', 0 union all
Select 'C', 3 Union all
Select 'B', 2

Select T.Cat,T.Value,Case When T.Cat=TT.Cat Then TT.min Else T.min End from ((Select *,0 As min From #t) as T Left outer join (
Select Cat,Min(Value) As Value,1 As min From #t Group by CAt) as tt On T.CAt=TT.cat and T.value=tt.value)

In Love... With Me!

man889
Starting Member

25 Posts

Posted - 2011-05-20 : 03:50:07

Hi raghuveer125,

Your solution is much better, I will try it.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources