Any possible optimization for the following SQL statement

Hi all,
I am relatively new to this group and forgive me if this question is
too trivial...
Say, I have an audit table AccountAudit that contains account records.
When each batch of rows are inserted, the timestamp is saved at
UpdateTime column. At a certain timestamp, there are multiple rows for
an account.
Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
most recent groups of rows. Here is my SQL statement. I wonder if there
is any way to optimize it.
select * from AccountAudit where Account = 4 and ActionType = 'C'
and UpdatedTime in
(select distinct top 2 UpdatedTime from AccountAudit
where Account = 4 and ActionType = 'C'
order by UpdatedTime desc)
Thanks in advance.
FrankYou could also do this in a single SELECT instead of the nested query.
SELECT DISTINCT TOP 2 Account, ActionType, UpdatedTime
FROM Accounts
WHERE Account = 4 and ActionType = 'C'
ORDER BY UpdatedTime DESC|||To add to Lubdha's response, it is also very important to consider indexing.
A composite index on Account, ActionType, UpdatedTime may help optimize this
particular query. You'll need to consider your overall workload in order to
determine the best indexing strategy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<fzhang@.calamos.com> wrote in message
news:1149881438.607379.14290@.i39g2000cwa.googlegroups.com...
> Hi all,
> I am relatively new to this group and forgive me if this question is
> too trivial...
> Say, I have an audit table AccountAudit that contains account records.
> When each batch of rows are inserted, the timestamp is saved at
> UpdateTime column. At a certain timestamp, there are multiple rows for
> an account.
> Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
> most recent groups of rows. Here is my SQL statement. I wonder if there
> is any way to optimize it.
> select * from AccountAudit where Account = 4 and ActionType = 'C'
> and UpdatedTime in
> (select distinct top 2 UpdatedTime from AccountAudit
> where Account = 4 and ActionType = 'C'
> order by UpdatedTime desc)
>
> Thanks in advance.
> Frank
>