SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joins and getting the "earliest" record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts

Posted - 12/13/2012 :  16:07:40  Show Profile  Reply with Quote
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
Posting Yak Master

101 Posts

Posted - 12/13/2012 :  16:37:09  Show Profile  Reply with Quote
SELECT OrderID
,Status
,Location
,DateReceived
FROM tablename
WHERE DateReceived IN (SELECT MAX(DateReceived) DateReceived
FROM tablename)
Go to Top of Page

tech_1
Posting Yak Master

105 Posts

Posted - 12/13/2012 :  17:24:34  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/13/2012 :  19:44:16  Show Profile  Reply with Quote

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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/13/2012 :  20:24:52  Show Profile  Reply with Quote
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/


Go to Top of Page

RichardAnderton
Starting Member

15 Posts

Posted - 12/16/2012 :  17:17:39  Show Profile  Reply with Quote
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')
Go to Top of Page

babloo
Starting Member

USA
35 Posts

Posted - 12/17/2012 :  16:05:30  Show Profile  Reply with Quote
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
Go to Top of Page

babloo
Starting Member

USA
35 Posts

Posted - 12/17/2012 :  16:16:09  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/17/2012 :  17:53:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000