Help With Update Please

I have a table called table1 that looks like this:
CName, Calls,Lost
--,--,--
Test, 18
Test1, 3
Test2, 4
Test5, 23
Test6, 21
Test7, 8
Test8, 4
Test9, 2
Test10, 23
I need to set Lost to 1 (True) ONLY where Calls is the minimum. So in this
case Lost would be equal to 1 for record Test9 ecause Calls equals 2 (the
minimum). HOWEVER, if another Calls equaled 2 ( the lowest number) then I
wouls not set Lost equal to 1 for any row.
Basically I need to set Lost equal to 1 only where Calls is the lowest
without a tie.
Any thoughts?
thank you.One solution would be to...
Update Table
Set Lost = 1
Where (Calls = (Select min(Calls) From Table))
Depending on how you want this to work if there were two records with Calls
= 2.
"Bill" <msnews.microsoft.com> wrote in message
news:eM2SiFtvFHA.3864@.TK2MSFTNGP12.phx.gbl...
>I have a table called table1 that looks like this:
> CName, Calls,Lost
> --,--,--
> Test, 18
> Test1, 3
> Test2, 4
> Test5, 23
> Test6, 21
> Test7, 8
> Test8, 4
> Test9, 2
> Test10, 23
> I need to set Lost to 1 (True) ONLY where Calls is the minimum. So in
> this case Lost would be equal to 1 for record Test9 ecause Calls equals 2
> (the minimum). HOWEVER, if another Calls equaled 2 ( the lowest number)
> then I wouls not set Lost equal to 1 for any row.
> Basically I need to set Lost equal to 1 only where Calls is the lowest
> without a tie.
> Any thoughts?
> thank you.
>
>|||Thanks. However, I only want to update the row where no other roh has the
lowest Calls.
"Marshall" <marshall@.newsgroup.nospam> wrote in message
news:uV%23NxKtvFHA.1996@.TK2MSFTNGP10.phx.gbl...
> One solution would be to...
> Update Table
> Set Lost = 1
> Where (Calls = (Select min(Calls) From Table))
> Depending on how you want this to work if there were two records with
> Calls = 2.
> "Bill" <msnews.microsoft.com> wrote in message
> news:eM2SiFtvFHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||So If I understand correctly, If you had the dataset of:
CName, Calls,Lost
--,--,--
Test, 18
Test1, 3
Test2, 4
Test5, 23
Test6, 21
Test7, 8
Test8, 4
Test9, 2
Test10, 23
Test11, 2
Where Test9 and Test11 both have calls=2. In this case which row would get
updated?
"Bill" <msnews.microsoft.com> wrote in message
news:uMacKOtvFHA.2960@.tk2msftngp13.phx.gbl...
> Thanks. However, I only want to update the row where no other roh has the
> lowest Calls.
>
> "Marshall" <marshall@.newsgroup.nospam> wrote in message
> news:uV%23NxKtvFHA.1996@.TK2MSFTNGP10.phx.gbl...
>|||As I understand it neither of the rows would be updated.|||Yes. Lost should equal 1 ONLY where the row has the lowest calls but is not
tied.
Eg.
test1,8,0
test2,5,0
test9,5,0
but...
test1,8,0
test2,5,1
test9,6,0
Calls has to be tthe very lowest without a tie in order for lost to be 1
"Marshall" <marshall@.newsgroup.nospam> wrote in message
news:%235pq4btvFHA.908@.tk2msftngp13.phx.gbl...
> So If I understand correctly, If you had the dataset of:
> CName, Calls,Lost
> --,--,--
> Test, 18
> Test1, 3
> Test2, 4
> Test5, 23
> Test6, 21
> Test7, 8
> Test8, 4
> Test9, 2
> Test10, 23
> Test11, 2
> Where Test9 and Test11 both have calls=2. In this case which row would
> get updated?
> "Bill" <msnews.microsoft.com> wrote in message
> news:uMacKOtvFHA.2960@.tk2msftngp13.phx.gbl...
>|||Actually, in the following...
CName, Calls,Lost
--,--,--
Test, 18
Test1, 3
Test2, 4
Test5, 23
Test6, 21
Test7, 8
Test8, 4
Test9, 2
Test10, 23
Test9 row would be 1 because 2 is the lowest calls and there are no other
rows with 2 as the lowest calls.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127326106.801682.12070@.z14g2000cwz.googlegroups.com...
> As I understand it neither of the rows would be updated.
>|||What about this? It's not pretty but I think it works how you want it
too.
If (Select count(*) From t
Where Calls = (Select Min(Calls) From t)) = 1
Begin
Update t
Set Lost = Null
Update t
Set Lost = 1
Where Calls = (Select Min(Calls) From T)
End
Else
Begin
Update t
Set Lost = Null
End
HTH
Barry|||Think of it a different way...
Team,Score,WinLoseOrTie
team1,18,nowin
team2,23,nowin
team3,10,win
team4,50,nowin
team,5,23,nowin
BUT...
Team,Score,WinLoseOrTie
team1,18,nowin
team2,23,nowin
team3,10,nowin
team4,50,nowin
team,5,10,nowin
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127326106.801682.12070@.z14g2000cwz.googlegroups.com...
> As I understand it neither of the rows would be updated.
>|||Did this work?