sql minimum of all positive values

[learner45]

Hi I have a table like this:

C_ID Cost Is_employee
100 234 True
134 -587 False
120 457 True
......

Is there a way to find out the row where Is_employee is "True" and Cost is (Mimimum and Positive)?

Thanks

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (C_ID [COLOR=blue]int[/color],  [COLOR=blue]Cost[/color] [COLOR=blue]int[/color], Is_employee [COLOR=blue]varchar[/color](10))

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (100, 234,[COLOR=red]'True'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (134, -587,[COLOR=red]'False'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (120, 457,[COLOR=red]'True'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] (135, -1,[COLOR=red]'True'[/color])


[COLOR=blue]SELECT[/color] Test.*
[COLOR=blue]FROM[/color] @Test Test
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MIN[/color]([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=blue]Cost[/color] < 0 [COLOR=blue]THEN[/color] 9999999 [COLOR=blue]ELSE[/color] [COLOR=blue]Cost[/color] [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [COLOR=blue]Cost[/color]
                   [COLOR=blue]FROM[/color] @Test
            [COLOR=blue]WHERE[/color] Is_employee = [COLOR=red]'True'[/color]) tst
      [COLOR=blue]ON[/color] Test.Cost = Tst.Cost
[COLOR=blue]WHERE[/color] Test.Is_employee = [COLOR=red]'True'[/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Since aggregates ignore nulls, I would suggest a very minor change to Boris's suggestion.

change:

CASE WHEN Cost < 0 THEN 9999999 ELSE Cost END

to:

CASE WHEN Cost < 0 THEN [!]NULL[/!] ELSE Cost END

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I was having troubles with preposition (?). Borislav's sounds OK to me, but Boris's somehow doesn't look right.

 

[bborissov]

Boris'



S****t I forgot about NULLs (Friday evening here )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

I was hoping that everyone would focus on the code, and not my (lack of) writing skills.

Happy Friday evening, Boris. Cheers to you and yours from this side of the world!



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

If you have lack of writing skills then I can't write at all
We have BEER now!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[learner45]

Thanks a lot Everyone!!!!!!