January 13, 2011 at 10:34 am
Hello every one, can I rewrite the following query without using the 'ROW_NUMBER() OVER ' part.
The query is supposed to pull out the records whose CODE is not NULL and has most
recent date for UPDATE_DATE . The reason I wanted to do this is, When I embed this query
in between many other queries along with JOINs, My oracle server is unable to execute. So, I thought
its better to supplant 'ROW_NUMBER() OVER ' logic with something else and try it. .
SELECT a.* FROM
(SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.PIDM
ORDER BY b.UPDATE_DATE DESC) AS Rno
FROM
(
SELECT *
FROM SHYNCRO WHERE CODE IS NOT NULL
)b
)a
WHERE a.Rno = 1
January 13, 2011 at 11:07 am
Info on the primary key would be helpful
January 13, 2011 at 11:47 am
mounish (1/13/2011)
When I embed this query in between many other queries along with JOINs, My oracle server is unable to execute.
Just an FYI: You realize you posted this in SQL 2k8 forum, not the oracle forum? You'd probably get better and more effective response there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 13, 2011 at 11:51 am
Sure, you can use a TOP 1 with an ORDER BY. Something like this:
SELECT y.whatever
FROM MyTable
WHERE ID = (select top(1) ID
from mytable
order by mydate desc) as y
--or in a join like this
SELECT y.whatever
FROM MyOtherTable AS x
JOIN MyTable AS y
ON x.MyId = y.MyId
AND y.YourID = (SELECT TOP(1) YourID
FROM MyTable AS y2
ORDER BY y2.MyDate DESC)
WHERE....
You can also use MAX, but that is frequently converted to TOP anyway, so why not use it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply