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_Dateselect 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_DateThank you very much for the helpful info. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-24 : 19:52:45
|
with cteAS ( {your query here})select * from cte where rn = 1; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
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_Dateselect COMPANY,POSITION,DESCRIPTION,POSIT_STATUS,EFFECT_DATE,END_DATE,JOB_CODE,DEPARTMENT,DEPT_NAME,AS_OF_DATE, DATE_STAMP, STATUS_FLAGfrom ( 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 TWHERE ROWNUM = 1 Thank you very much for the helpful info. |
|
|
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 1COMPANY,POSITION,DESCRIPTION,POSIT_STATUS,EFFECT_DATE,END_DATE,JOB_CODE,DEPARTMENT,DEPT_NAME,AS_OF_DATE,DATE_STAMP,STATUS_FLAGfrom LBI_LW_POSITIONS where company=@company and position=@position and effect_date=@effect_DateORDER BY DATE_STAMP DESC[/CODE]To be optimal, you will want to have an index on COMPANY, POSITION, EFFECT_DATE, DATE_STAMP DESC. |
|
|
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 ROWNUMINTO #TEMPfrom PAPOSITIONS WHERE company=@company and position=@position and effect_date=@effect_DateSELECT * FROM #TEMP WHERE ROWNUM = 1P.Siva |
|
|
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_FLAGfrom PAPOSITIONS WHERE [Row Index] = 1P.Kameswara rao |
|
|
|
|
|