SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Want to use row_number partition with where condit
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

540 Posts

Posted - 08/24/2013 :  17:06:04  Show Profile  Reply with Quote
I want to get the top 1 row, using below select query based on

three variable values: @Company, @Position, @Effect_Date

select COMPANY,
POSITION,
DESCRIPTION,
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
AS_OF_DATE, DATE_STAMP, STATUS_FLAG
from PAPOSITIONS

row_number() over(partition by company, position, effect_date order by date_stamp desc)

where company=@company and position=@position and effect_date=@effect_Date


Thank you very much for the helpful info.

Edited by - cplusplus on 08/24/2013 18:29:46

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/24/2013 :  19:52:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
with cte
AS (
{your query here}
)
select *
from cte where rn = 1;


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

cplusplus
Aged Yak Warrior

540 Posts

Posted - 08/24/2013 :  22:29:54  Show Profile  Reply with Quote
Is this the proper way to write the query using row_number partition.

I am not sure with passing three variable values inside.
where company=@company and position=@position and effect_date=@effect_Date



select COMPANY,
POSITION,
DESCRIPTION,
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
AS_OF_DATE, DATE_STAMP, STATUS_FLAG
from (
	select COMPANY,
		POSITION,
		DESCRIPTION,
		POSIT_STATUS,
		EFFECT_DATE,
		END_DATE,
		JOB_CODE,
		DEPARTMENT,
		DEPT_NAME,
		AS_OF_DATE, DATE_STAMP, STATUS_FLAG, 
		ROW_NUMBER() OVER(PARTITION by COMPANY, POSITION, EFFECT_DATE ORDER BY DATE_STAMP DESC) AS ROWNUM
	from LBI_LW_POSITIONS where company=@company and position=@position and effect_date=@effect_Date
	) as T
WHERE ROWNUM = 1



Thank you very much for the helpful info.

Edited by - cplusplus on 08/24/2013 22:53:04
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
339 Posts

Posted - 08/26/2013 :  12:34:56  Show Profile  Reply with Quote
To be honest, you don't need ROW_NUMBER for this. Since you are specifying all of the columns used in the PARTITION BY clause within the WHERE criteria, all you are looking to do is get the TOP 1. This can be accomplished very simply:

select TOP 1
COMPANY,
POSITION,
DESCRIPTION,
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
AS_OF_DATE,
DATE_STAMP,
STATUS_FLAG
from LBI_LW_POSITIONS 
where company=@company and position=@position and effect_date=@effect_Date
ORDER BY DATE_STAMP DESC


To be optimal, you will want to have an index on COMPANY, POSITION, EFFECT_DATE, DATE_STAMP DESC.
Go to Top of Page

sivadss2007
Starting Member

India
18 Posts

Posted - 08/28/2013 :  04:43:39  Show Profile  Reply with Quote
select COMPANY,
POSITION,
DESCRIPTION,
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
AS_OF_DATE, DATE_STAMP, STATUS_FLAG,
row_number() over(partition by company, position, effect_date order by date_stamp desc) AS ROWNUM
INTO #TEMP
from PAPOSITIONS
WHERE company=@company and position=@position and effect_date=@effect_Date

SELECT * FROM #TEMP WHERE ROWNUM = 1



P.Siva
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/28/2013 :  05:34:55  Show Profile  Reply with Quote
select
ROW_NUMBER() OVER(PARTITION BY JOB_CODE,DEPARTMENT ORDER BY EFFECT_DATE) AS 'Row Index'
COMPANY,
POSITION,
DESCRIPTION,
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
AS_OF_DATE, DATE_STAMP, STATUS_FLAG
from PAPOSITIONS
WHERE
[Row Index] = 1

P.Kameswara rao
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000