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
>