Joins and getting the "earliest" record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joins and getting the "earliest" record

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2012-12-13 : 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
Posting Yak Master

103 Posts

Posted - 2012-12-13 : 16:37:09

SELECT OrderID
,Status
,Location
,DateReceived
FROM tablename
WHERE DateReceived IN (SELECT MAX(DateReceived) DateReceived
FROM tablename)

tech_1
Posting Yak Master

129 Posts

Posted - 2012-12-13 : 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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-13 : 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 - 2012-12-16 : 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

35 Posts

Posted - 2012-12-17 : 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

35 Posts

Posted - 2012-12-17 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-17 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources