Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Want to use row_number partition with where condit

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-24 : 17:06:04
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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-24 : 19:52:45
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

567 Posts

Posted - 2013-08-24 : 22:29:54
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.
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-26 : 12:34:56
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:

[CODE]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[/CODE]

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

18 Posts

Posted - 2013-08-28 : 04:43:39
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

19 Posts

Posted - 2013-08-28 : 05:34:55
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
   

- Advertisement -