| Author |
 Topic  |
|
|
tech_1
Posting Yak Master
105 Posts |
 Posted - 12/13/2012 : 16:07:40
|
I have a query which needs to bring back the earliest record in the resultset. The original query has multiple joins and when adding another join, it brings duplicates so to make it easier, I am trying to take that table in question as an example and do the following:
I want to add a clause where the WO.DateReceived is the earliest record within the resultset. Thoughts?
SELECT WO.OrderID, WO.Status, WO.Location, WO.DateReceived
sure, brings back a few records BUT I want the earliest one found on the DateReceived column
|
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 12/13/2012 : 16:37:09
|
SELECT OrderID
,Status
,Location
,DateReceived
FROM tablename
WHERE DateReceived IN (SELECT MAX(DateReceived) DateReceived
FROM tablename) |
 |
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/13/2012 : 17:24:34
|
thanks but the performance is awful on that. just totally awful... takes quite a while to filter through 126497 records
and it also didnt work :(
here is what I have:
SELECT
WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed, WorkOrder.DateReceived
FROM WorkOrder
WHERE ControlNumber = '0001' AND WorkOrder.WOClosed = 0 AND (WorkORder.Status <> 'DELE' OR WorkORder.Status <> 'CELI' OR WorkOrder.Status <> 'BLQA') AND DateReceived IN (SELECT MAX(DateReceived) DateReceived FROM WorkOrder)
without the DateReceived IN query, it returns 5 records. With the expected alteration, I would expect it to return 1 record which has the earliest date from the 5 records |
Edited by - tech_1 on 12/13/2012 17:31:25 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/13/2012 : 19:44:16
|
do this
Select t.* from Table t
inner join
(Select OrderId,MAX(DateReceived)MAXRecieved
from Table
Group by OrderId
)P on P.OrderId = t.OrderId and t.DateReceived = P.MAXRecieved |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3829 Posts |
Posted - 12/13/2012 : 20:24:52
|
Can you just use the TOP operator?
SELECT TOP 1
WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed, WorkOrder.DateReceived
FROM WorkOrder
WHERE ControlNumber = '0001' AND WorkOrder.WOClosed = 0 AND (WorkORder.Status <> 'DELE' OR WorkORder.Status <> 'CELI' OR WorkOrder.Status <> 'BLQA')
ORDER BY WorkOrder.DateReceived DESC
If not, please provide DDL, DML and expected output. Here are some links that can help you compile that information:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/
|
|
|
|
RichardAnderton
Starting Member
15 Posts |
Posted - 12/16/2012 : 17:17:39
|
If you have multiple lines returned by design (ie many rows that meet your criteria), and are looking for the date corresponding to the first occurance of the records that match your criteria, then I would go for cross apply - I don't have a SQL server to hand, so sorry if this is wrong:
SELECT a.WorkOrderID, a.WOStatus, a.WOLocation, a.WOClosed, b.DateReceived
FROM WorkOrder as a
CROSS APPLY (select top 1 B.DateReceived from WorkOrder B where a.ControlNumber = B.ControlNumber and a.WOClosed = B.WOClosed and a.Status = B.Status order by B.DateReceived DESC) as b
WHERE ControlNumber = '0001' AND
WorkOrder.WOClosed = 0 AND
(WorkORder.Status <> 'DELE' OR
WorkORder.Status <> 'CELI' OR
WorkOrder.Status <> 'BLQA')
|
|
|
|
babloo
Starting Member
USA
26 Posts |
Posted - 12/17/2012 : 16:05:30
|
SELECT WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed, Max(WorkOrder.DateReceived) as LatestDate
FROM WorkOrder
WHERE ControlNumber = '0001'
AND WorkOrder.WOClosed = '0'
AND WorkORder.Status NOT IN ('DELE', 'CELI', 'BLQA')
Group by WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed
Order by LatestDate desc |
|
|
|
babloo
Starting Member
USA
26 Posts |
Posted - 12/17/2012 : 16:16:09
|
Just to speed up your query restrict date/time:
SELECT WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed, Max(WorkOrder.DateReceived) as LatestDate
FROM WorkOrder
WHERE ControlNumber = '0001'
AND WorkOrder.WOClosed = '0'
AND WorkORder.Status NOT IN ('DELE', 'CELI', 'BLQA')
--This will go back just 1 day change this to as much as you want.
AND WorkOrder.DateReceived >= CONVERT(VARCHAR, GETDATE() -1, 101)
Group by WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed
Order by LatestDate desc
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3829 Posts |
Posted - 12/17/2012 : 17:53:20
|
quote:
Originally posted by babloo
Just to speed up your query restrict date/time:
SELECT WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed, Max(WorkOrder.DateReceived) as LatestDate
FROM WorkOrder
WHERE ControlNumber = '0001'
AND WorkOrder.WOClosed = '0'
AND WorkORder.Status NOT IN ('DELE', 'CELI', 'BLQA')
--This will go back just 1 day change this to as much as you want.
AND WorkOrder.DateReceived >= CONVERT(VARCHAR, GETDATE() -1, 101)
Group by WorkOrder.WorkOrderID, WorkOrder.WOStatus, WorkOrder.WOLocation, WorkOrder.WOClosed
Order by LatestDate desc
What are you converting a Date to a Varchar? You should always work with proper datatypes if at all posible.
Also, according to teh OP, they want the "earliest" row. Not sure wht that means without any data, but, I'd assume that is the MIN or TOP 1 .. ORDER BY Date ASC. |
Edited by - Lamprey on 12/17/2012 17:57:34 |
|
|
| |
Topic |
|
|
|
Joins and getting the "earliest" record
