March 23, 2005 at 6:51 am
March 23, 2005 at 7:15 am
So, you're looking for the TOP 2 of each group? Try this:
set nocount on
use northwind
select
t1.CustomerID
, t1.OrderDate
from
orders t1
where
t1.OrderDate in
(
select top 2 --with ties
t2.OrderDate
from
orders t2
where
t2.CustomerID = t1.CustomerID
order by
t2.OrderDate desc
)
order by
t1.CustomerID
, t1.OrderDate desc
set nocount off
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 23, 2005 at 7:42 am
I have another way to solve this.
select CustomerID, Orderdate
from orders A where A.Orderdate in
(select Top 2 B.orderdate from orders B where A.customerId = B.customerId)
order by CustomerID, OrderDate desc
Which query is better performance wise ? I have 370,000 rows in the table and I need top 100 data.
please let me know which one is better so that I can use that query
Regards
Meghana
March 23, 2005 at 7:46 am
run both and see for yourself, just make sure you have all the indexes you need to run each query.
March 23, 2005 at 7:55 am
The only difference I see between your statement and mine, is that I do a SELECT TOP 2 ...ORDER BY ...DESC, while you don't. Both execution plans are the same, except for the sort for my ORDER BY.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 23, 2005 at 7:57 am
Btw, you should really add an ORDER BY when you select your TOP 2, otherwise your resultset is not predictable and meaningless.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply