January 10, 2011 at 11:14 am
Hello,
I have a table with columns ID, R_DATE, CATEGORY. What I have to do is to pull out all the records with most recent R_DATE and CATEGORY as one of 'CC' or 'MB'
ID R_DATE CATEGORY
--------------------------------
D142 12/12/08 CC
S213 10/11/08 CA
S563 07/08/10 MB
S421 05/21/09 MB
F367 01/30/07 CA
D142 08/24/07 MB
What I have done is
STEP:1 (Pulls out all records with CATEGORY value as 'CC' or 'MB')
INSERT INTO STABLE_TMP
( ID,R_DATE,CATEGORY)
SELECT DISTINCT
a.ID,a.R_DATE,a.CATEGORY,
from
( SELECT
ID,R_DATE,CATEGORY
FROM STABLE where CATEGORY = 'CC'
OR CATEGORY = 'MB' )a
STEP:2 (Pulls out the records with most recent R_DATE from the resulting table of STEP: 1)
SELECT
a.ID,a.R_DATE,a.CATEGORY,
from
(select ID,R_DATE,CATEGORY ROW_NUMBER() over (partition by ID
order by R_DATE desc) as Rno
from STABLE_TMP
)a
WHERE a.Rno=1
Everything works fine. But, Can I accomplish the task without having the STABLE_TMP in between. Can I optimize it further??
January 10, 2011 at 11:56 am
You could either use a subquery with MAX() and GROUP BY or change the PARTITION BY part in your ROW_NUMBER subquery and use it directly against your source table.
As a side note: this is homework, right?
January 10, 2011 at 12:41 pm
I got the solution.
@LutzM: Thanks for ur hlp. Its not the homework BTW, the problem is far more complex.
I jz drilled it down to look like simple one.
The following will work
select a.ID,a.R_DATE,a.CATEGORY from
(select b.ID,
b.R_DATE,
b.CATEGORY,
ROW_NUMBER() over (partition by b.ID
order by b.R_DATE desc) as Rno
from
( SELECT
ID, R_DATE, CATEGORY FROM STABLE
where SHRNCRS_NCST_CODE = 'CC'
OR SHRNCRS_NCST_CODE = 'MB'
)b
)a
where a.Rno = 1
January 10, 2011 at 12:58 pm
Wouldn't the following return the same result?
;WITH cte AS
(
SELECT
ID,
R_DATE,
CATEGORY,
ROW_NUMBER() OVER (PARTITION BY SHRNCRS_NCST_CODEORDER BY R_DATE DESC) Rno
FROM STABLE
WHERE SHRNCRS_NCST_CODE IN('CC', 'MB')
)
SELECT *
FROM cte
WHERE Rno=1
January 10, 2011 at 1:29 pm
Well, Even though I am not good at using CT Expressions, According to my knowledge, the following should work. But, if I try this, Its showing error "FROM keyword not found where expected"
WITH cte AS
(
SELECT
*, ROW_NUMBER() over (partition by ID
order by R_DATE desc) as Rno
FROM STABLE
WHERE SHRNCRS_NCST_CODE IN('CC', 'MB')
)
SELECT *
FROM cte
WHERE Rno=1
January 10, 2011 at 2:58 pm
Strange. Should work. Is this the full query you posted?
January 10, 2011 at 3:00 pm
If this isn't the first statement in the batch, you need a semicolon.
Change:
WITH cte AS
to:
;WITH cte AS
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 10, 2011 at 3:03 pm
Tried that too, Still no change !!
January 10, 2011 at 3:31 pm
Here's a test scenario showing that your code should work.
Beside the table name, what's different on your end?
DECLARE @tbl TABLE
(
ID INT,R_DATE DATETIME, SHRNCRS_NCST_CODE CHAR(2)
)
INSERT INTO @tbl
SELECT 1,'12/12/08 ','CC' UNION ALL
SELECT 1,'12/14/08 ','CC'
;WITH cte AS
(
SELECT
*, ROW_NUMBER() OVER (PARTITION BY ID
ORDER BY R_DATE DESC) AS Rno
FROM @tbl
WHERE SHRNCRS_NCST_CODE IN('CC', 'MB')
)
SELECT *
FROM cte
WHERE Rno=1
January 10, 2011 at 4:00 pm
mounish (1/10/2011)
Tried that too, Still no change !!
Heh, and if you can't easily compare what you have to Lutz, it sounds like you've buried this in a larger query... can you pop the entire query up? My guess is it's complaining about a different line that might have gotten bumped while you were working on this.
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 10, 2011 at 4:06 pm
mounish (1/10/2011)
Well, Even though I am not good at using CT Expressions, According to my knowledge, the following should work. But, if I try this, Its showing error "FROM keyword not found where expected"
Isn't that an Oracle error message?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 10, 2011 at 4:20 pm
mister.magoo (1/10/2011)
mounish (1/10/2011)
Well, Even though I am not good at using CT Expressions, According to my knowledge, the following should work. But, if I try this, Its showing error "FROM keyword not found where expected"Isn't that an Oracle error message?
select *
where blah
from syscolumns
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Errr, you might be right. WITH is a T-SQL convention. So is Row_Number() afaik.
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 10, 2011 at 7:07 pm
Craig Farrell (1/10/2011)
mister.magoo (1/10/2011)
mounish (1/10/2011)
Well, Even though I am not good at using CT Expressions, According to my knowledge, the following should work. But, if I try this, Its showing error "FROM keyword not found where expected"Isn't that an Oracle error message?
select *where blah
from syscolumns
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Errr, you might be right. WITH is a T-SQL convention. So is Row_Number() afaik.
Actually, WITH was available in Oracle long before SQL Server. They don't call it a "CTE", though. Instead, they call it "Sub-Query Refactoring" and it works the same way as it does in SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2011 at 9:55 pm
Jeff Moden (1/10/2011)
Actually, WITH was available in Oracle long before SQL Server. They don't call it a "CTE", though. Instead, they call it "Sub-Query Refactoring" and it works the same way as it does in SQL Server.
Shows ya what I know, I guess. Thanks for the correction. 🙂
*mutters something about not talking about RDBMS's he doesn't know anymore...*
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 11, 2011 at 10:19 am
Thank y'all Mates for your help.At this moment, I guess I should go with the solution I posted earlier even though, it is not simple.
As you can see there, the solution query don't uses CTE (though it is simple).. Thanks Again!:-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply