easy question about a select

hi, I think this is an easy question
I have something like this in my DB
Amount1 Amount2 Date
100 100 01/01/2005
100 230 01/02/2005
200 0 01/03/2005
444 555 01/04/2005 <--I wanna get this row
666 0 01/05/2005
I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
example it'd be (444 555 01/04/2005)
So I have made
SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
but I dont know how to specify the last Date that match the conditions befor
e
any sug
thksHello Kenny M.,
Try
SELECT TOP 1
[Amount1],
[Amount2],
[Date]
FROM myTable
WHERE
[A,ount1] <> 0
AND [Amount2] <> 0
ORDER BY
[Date] DESC
Aaron Weiker
http://aaronweiker.com/

> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2
> <> 0
> but I dont know how to specify the last Date that match the conditions
> before
> any sug
> thks
>|||select max(Date)
from MyTable
where Amount1 <> 0 and Amount2 <> 0
Adi|||Hi Kenny,
You could use the following :
select amount1,amount2,mydate
from
mytable where mydate = (select max(mydate) as MaxDate from mytable where
amount1 <> 0 and amount2 <> 0)
Cheers,
Andy
"Kenny M." wrote:

> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions bef
ore
> any sug
> thks
>
> --
>|||If amount is always 0 or positive I would do..
select max(date) where amount1 >0 and amount2>0
This might give you a better execution plan than using <>
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:465C1809-0618-4F69-9406-09D35664871C@.microsoft.com...
> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions
before
> any sug
> thks
>
> --
>|||If one row for each date then you can write like this
Select Amount1,Amount2 from Table1 where [Date] = (Select Max([Date]) from
Table1 where Amount1<> 0 and Amount2 <> 0)
Hth
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:465C1809-0618-4F69-9406-09D35664871C@.microsoft.com...
> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions
before
> any sug
> thks
>
> --
>|||For sure you meant:
...
where amount1 > amount2 or amount1 < amount2
AMB
"Wayne Snyder" wrote:

> If amount is always 0 or positive I would do..
> select max(date) where amount1 >0 and amount2>0
> This might give you a better execution plan than using <>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:465C1809-0618-4F69-9406-09D35664871C@.microsoft.com...
> before
>
>|||thks all of you guys
"Kenny M." wrote:

> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions bef
ore
> any sug
> thks
>
> --
>