March 4, 2013 at 5:12 pm
Hello comunity
I have write this TSQL, but my problema that is return more than one LastPrice for the same REF:
select distinct sl.ref as 'Ref', epcpond as 'epcpond',max(ousrdata + ousrhora) as 'Maxdate'
from
sl inner join planOref x on Rtrim(sl.ref) = RTRIM(x.ref)
where
epcpond <> 0
and sl.datalc <= '20130104'
group by sl.ref,epcpond
order by ref asc
I know the problem is on the Group by, but i can´t use SUM, MAX or MIN on field LastPrice, the result is for exemple:
REF LastPrice MaxDate
2009461 3.642000 2012-09-04 16:14:11.000
2009461 3.640000 2012-09-04 16:19:24.000 -- This is the correct value and lastEntry
The unique and correct value is the last because the MaxDate is greather than the first.
Any idea to solve my problem!
Many thanks
Luis Santos
March 4, 2013 at 5:32 pm
Hello again
I find the solution with this:
WITH CTE
AS
(SELECT sl.ref, epcpond,
ROW_NUMBER() OVER(PARTITION BY sl.ref ORDER BY (ousrdata + ousrhora) DESC) as EndRec
FROM sl inner join planOref x on Rtrim(sl.ref) = RTRIM(x.ref)
where
epcpond <> 0
and sl.datalc <= '20130104')
SELECT ref,epcpond
FROM CTE WHERE EndRec=1
ORDER BY ref
But now , i need to make na UPDATE to my table planOref on field LastPrice.
What the best solution:
1.create a Temp Table with :
SELECT ref,epcpond
FROM CTE WHERE EndRec=1
ORDER BY ref
2. or directly use the CTE and make my Update statment.
What you suggest ?
Thanks
Luis Santos
March 4, 2013 at 5:46 pm
If you can update it through the CTE (your option 2) it might be better. It may be necessary to reverse the order of the JOINed tables, i.e., make the target table the left table. As long as the UPDATE is making an unambiguous choice of record, it may allow it either way.
Another way is something like:
UPDATE t
SET field2update=value
FROM target t
INNER JOIN (
SELECT *, n=ROW_NUMBER() OVER ...
FROM source
) s ON t.key = s.key
WHERE n=1
If you were to provide some DDL and sample data, it would be easy enough to put together an example.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply