January 27, 2011 at 10:35 am
i have a table as below
empid orderdateorderid
0001 2010-11-03 00:00:00abcd
0001 2010-11-09 00:00:00abcd
0002 2010-11-01 00:00:00efgh
0002 2010-11-02 00:00:00efgh
00022010-11-03 00:00:00efgh
0002 2010-11-01 00:00:00ijkl
0002 2010-11-02 00:00:00ijkl
0002 2010-11-01 00:00:00mnop
0002 2010-11-02 00:00:00mnop
A001 2010-11-10 00:00:00qrst
A001 2010-11-10 00:00:00qrst
A001 2010-11-11 00:00:00qrst
A001 2010-11-12 00:00:00qrst
this is how the result should be. only the records with orderdate which are less than the max orderdate for each order should be selected.
empid orderdateorderid
0001 2010-11-03 00:00:00abcd
0002 2010-11-01 00:00:00efgh
0002 2010-11-02 00:00:00efgh
0002 2010-11-01 00:00:00ijkl
0002 2010-11-01 00:00:00mnop
A001 2010-11-10 00:00:00qrst
A001 2010-11-10 00:00:00qrst
A001 2010-11-11 00:00:00qrst
Please help me with this. thanks in advance
January 27, 2011 at 11:15 am
What's the "max order date"? Is that a column in a table? A variable? An input parameter?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2011 at 11:45 am
the max orderdate is the maximum date for each order id for example
if u look at the table for orderid efgh there are three records and the maximum orderdate for those 3 records is
2010-11-03 00:00:00. so now i want only the records which have orderdate less than this date for this orderid. and so on for the remaining orderid's....
January 27, 2011 at 12:33 pm
;WITH MaxDates (OrderID, MaxDate) AS
(SELECT OrderID, MAX(OrderDate)
FROM MyTable
GROUP BY OrderID)
SELECT T1.*
FROM MyTable AS T1
LEFT JOIN CTE
ON T1.OrderID = CTE.OrderID
AND T1.OrderDate = CTE.MaxDate
WHERE CTE.OrderID IS NULL;
The "CTE" (Common Table Expression) picks the max order date for each order ID. Then you join to that using both order ID and order date in the From clause, using a join on both columns. Since it's an outer join, it will give you all the rows from MyTable. Then the Where clause makes it get rid of the rows that match the max order date for each order ID.
Make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2011 at 12:58 pm
Thank you....but im working on sql server 2000...sorry should've told you first...
January 27, 2011 at 1:47 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2011 at 1:52 pm
GSquared (1/27/2011)
;WITH MaxDates (OrderID, MaxDate) AS(SELECT OrderID, MAX(OrderDate)
FROM MyTable
GROUP BY OrderID)
SELECT T1.*
FROM MyTable AS T1
LEFT JOIN CTE
ON T1.OrderID = CTE.OrderID
AND T1.OrderDate = CTE.MaxDate
WHERE CTE.OrderID IS NULL;
This approach is inefficient, because you are forced to scan the table twice. You're much better off using Row_Number() or Rank().
WITH Ordered_Dates AS (
SELECT EmpID, OrderDate, OrderID
, Row_Number() OVER( PARTITION BY EmpID ORDER BY OrderDate DESC ) AS rn
FROM MyTable
)
SELECT EmpID, OrderDate, OrderID
FROM Ordered_Dates
WHERE rn > 1
Here are results from an actual table with about 300,000 rows.
Using Max()
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 2, logical reads 7830, physical reads 1, read-ahead reads 1372, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 766 ms, elapsed time = 7320 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Using Row_Number()
Table 'MyTable'. Scan count 1, logical reads 6426, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 46 ms, elapsed time = 6567 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Of course, this doesn't help the OP since he is still on SQL 2000.
BTW, there is a separate forum for SQL 2000/7.0. You would be better off posting your SQL 2000 questions there, so that you don't get SQL 2005 answers.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2011 at 2:01 pm
Thank you allen...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply