March 6, 2014 at 2:22 pm
I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011
MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
Any help with how to tackle this query would be greatly appreciated.
March 6, 2014 at 2:36 pm
A CTE with a row_number that partitions by machine would work for you. Not sure about your desired output you posted though. MachineA has a record with 4/2/2013, wouldn't that be the most recent one you want, not the 5/7/2011 you posted?
declare @mytable table
(
machinevarchar(10),
mydatedate
)
insert @mytable
select 'MachineA', '1/1/2008'
union all select 'MachineA', '1/3/2008'
union all select 'MachineB', '1/7/2008'
union all select 'MachineB', '1/8/2009'
union all select 'MachineB', '5/5/2010'
union all select 'MachineA', '5/7/2011'
union all select 'MachineA', '4/2/2013';
with cte as
(
select machine, mydate, row_number() over (partition by machine order by mydate desc) rn
from @mytable
)
select machine, mydate
from cte
where rn = 1
March 6, 2014 at 2:45 pm
Your posted expected correct results are not correct.
This is the max Date for each...
MachineMaxDate
MachineA 2013-04-02
MachineB 2010-05-05
This will get you there....
Create table #t1 ( id int identity primary key,
dString char(20)
)
Insert into #t1 ( dstring ) values ('MachineA 1/1/2008')
Insert into #t1 ( dstring ) values ('MachineA 1/3/2008')
Insert into #t1 ( dstring ) values ('MachineB 1/7/2008')
Insert into #t1 ( dstring ) values ('MachineB 1/8/2009')
Insert into #t1 ( dstring ) values ('MachineB 5/5/2010')
Insert into #t1 ( dstring ) values ('MachineA 5/7/2011')
Insert into #t1 ( dstring ) values ('MachineA 4/2/2013')
;with pCte as (
Selectsubstring(dString,1,charindex(' ',dString)) as Machine,
convert(date,substring(dstring,charindex(' ',dString),10)) as mDate
from #t1
)
SelectMachine, Max(mDate) as MaxDate
frompCte
group byMachine
drop table #t1
Hth,
..bob
March 6, 2014 at 3:19 pm
I did post my result wrong, thanks for noticing and attempting to help me.
I need to explain more clearly. The table I am selecting from contains thousands of records. There will also be many more combinations. I will not be able to write an insert or union for each.
My example here is just a simple example.
table name = sample
machine date
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
Something like --- SELECT DISTINCT machine, MAX date FROM sample
MachineA 4/2/2013
MachineB 5/5/2010
I need to retrieve the rows for each unique machine name that has the latest date.
I suspect what I am looking for may not be possible.
March 6, 2014 at 3:34 pm
defyant_2004 (3/6/2014)
I did post my result wrong, thanks for noticing and attempting to help me.I need to explain more clearly. The table I am selecting from contains thousands of records. There will also be many more combinations. I will not be able to write an insert or union for each.
My example here is just a simple example.
table name = sample
machine date
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
SELECT DISTINCT machine, MAX date FROM sample
MachineA 4/2/2013
MachineB 5/5/2010
I need to retrieve the rows for each unique machine name that has the latest date.
I suspect what I am looking for may not be possible.
The solution that Bob posted and the one I posted should both return what you are asking for. If they don't, let us know what they are doing that doesn't meet your criteria. The only reason we have inserts and unions is that we had to build your sample table since we don't have access to your data. You'll have to modify each query after that to fit your table and column names.
March 6, 2014 at 3:38 pm
If the record set was small, I could write a union or insert for each record like you have provided.
However, my real table has thousands of rows with multiple combinations.
I see and appreciate what you provided, but I am wondering how to do this for thousands of records without creating a thousand insert/unions statements.
March 6, 2014 at 3:43 pm
defyant_2004 (3/6/2014)
If the record set was small, I could write a union or insert for each record like you have provided.However, my real table has thousands of rows with multiple combinations.
I see and appreciate what you provided, but I am wondering how to do this for thousands of records without creating a thousand insert/unions statements.
You don't need the inserts and unions. We had to do that since you didn't provide us sample data and we don't have access to your tables. You'll have to take our queries from below that part and update the table and column names to what are in your table and you should get what you want. For example with my solution:
with cte as
(
select machine, mydate, row_number() over (partition by machine order by mydate desc) rn
from @mytable
)
select machine, mydate
from cte
where rn = 1
Where @mytable is your real table name, machine is the column name for your machines, and mydate is the date column in your table.
March 6, 2014 at 3:48 pm
Thanks for clarifying this!
I am going to give it a try.
March 6, 2014 at 5:59 pm
Excuse me from horning in here, but ROW_NUMBER() is overkill for this:
WITH SampleData (m,d) AS
(
select 'MachineA', '1/1/2008'
union all select 'MachineA', '1/3/2008'
union all select 'MachineB', '1/7/2008'
union all select 'MachineB', '1/8/2009'
union all select 'MachineB', '5/5/2010'
union all select 'MachineA', '5/7/2011'
union all select 'MachineA', '4/2/2013'
)
SELECT m, d=MAX(d)
FROM SampleData
GROUP BY m;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 7, 2014 at 1:05 pm
Thank you. This information really helped out. I appreciate all the feedback.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply