Top 4 Subquery
-
Cannot find subquiry for the case
TableName:
Address Date
1111 01/05/2004
1111 02/07/2005
1111 03/11/2005
etc. for every Address (can be more or less then 2)
I NEED ONLY TOP 2 QUERIES for the one Address where date is DESC
HOW CAN I CREATE a query with subquery?
-
cursor may come handy for the case.
-
Something like this?
SELECT Address, MAX([Date])
FROM dbo.foobar
GROUP BY Address
UNION ALL
SELECT foobar.Address, MAX([Date])
FROM dbo.foobar
INNER JOIN (
SELECT Address, MAX([Date]) MaxDate
FROM dbo.foobar
GROUP BY Address
) exclude
ON foobar.Address = exclude.Address
AND foobar.[Date] < exclude.MaxDate
GROUP BY foobar.Address
-
Next guess...
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] -
Thanks, It can help.
-
Works great, Frank.
Also see: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=119192&p=1
for similar solution.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply