ROW_NUMBER function - SQL Server Forums

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)

ROW_NUMBER function

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2012-02-16 : 11:27:57

I am running the following statement, but the records that are being returned are not as I expect.

Statement
select distinct Employer_Ref, client_Number, job_title,
ROW_NUMBER() OVER (PARTITION BY client_number,Job_Title Order By client_Number)
from Assignments
where Employer_Ref In ('OMWEEK','OWWEEK') and Actual_End = 0
order by Client_Number, Job_Title

Results as follows:
OMWEEK ABB_17 ELECTRICAL FITTER 1
OMWEEK ABB_17 ELECTRICAL FITTER 2
OMWEEK ABB_17 ELECTRICAL FITTER 3
OMWEEK ABB_17 ELECTRICAL FITTER 4

Whay I would like to see is 1 record as the first 3 colums are identical. Hope this makes sense.

Regards
Jannette

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 11:44:43

select distinct Employer_Ref, client_Number, job_title
from Assignments
where Employer_Ref In ('OMWEEK','OWWEEK') and Actual_End = 0
order by Job_Title

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2012-02-16 : 12:00:26

Further records shown are:
OMWEEK ABB_17 ELECTRICAL FITTER 1
OMWEEK ABB_17 ELECTRICAL FITTER 2
OMWEEK ABB_17 ELECTRICAL FITTER 3
OMWEEK ABB_17 ELECTRICAL FITTER 4
OMWEEK AC_M2 PROJECT MANAGER 1
OWWEEK ACOR4 PRODUCTION OPERATIVE 1
OWWEEK ACS~1 GENERAL OPERATIVE 1
OWWEEK ACS~1 GENERAL OPERATIVE 2
OWWEEK ACS~1 GENERAL OPERATIVE 3
OWWEEK ACS~1 GENERAL OPERATIVE 4
OWWEEK ACS~1 GENERAL OPERATIVE 5
OWWEEK ACS~1 GENERAL OPERATIVE 6
OMWEEK ABB_17 ELECTRICAL FITTER 1
OMWEEK ABB_17 ELECTRICAL FITTER 2
OMWEEK ABB_17 ELECTRICAL FITTER 3
OMWEEK ABB_17 ELECTRICAL FITTER 4
OMWEEK AC_M2 PROJECT MANAGER 1
OWWEEK ACOR4 PRODUCTION OPERATIVE 1
OWWEEK ACS~1 GENERAL OPERATIVE 1
OWWEEK ACS~1 GENERAL OPERATIVE 2
OWWEEK ACS~1 GENERAL OPERATIVE 3
OWWEEK ACS~1 GENERAL OPERATIVE 4
OWWEEK ACS~1 GENERAL OPERATIVE 5
OWWEEK ACS~1 GENERAL OPERATIVE 6
OWWEEK ACS~1 GENERAL OPERATIVE 7
OWWEEK ACS~1 GENERAL OPERATIVE 8
OWWEEK ACS~1 GENERAL OPERATIVE 9
OWWEEK ACS~1 GENERAL OPERATIVE 10
OWWEEK ACS~1 PRODUCTION OPERATIVE 1
OWWEEK ACS~1 PRODUCTION OPERATIVE 2
OWWEEK ACS~1 STRESS ENGINEER 1

I am trying to get a row count of all distinct records.

What I would like to see is:
OWWEEk ACS~1 GENERAL OPERATIVE 1
OWWEEk ACS~1 PRODUCTION OPERATIVE 2
OWWEEK ACS~1 STRESS ENGINEER 3

Hope this is a bit clearer

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:11:18

I am SO Lost

Do me a Favor

FORGET the Query you have

Give us the DDL of the Table(s)

Sample Data in DML Form

And what the expected results are suppose to be

And use [ code] [ /code] tags (without the space) to retain formatting

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 18:12:01

I think you simply need to use DENSE_RANK instead of ROW_NUMBER.

http://msdn.microsoft.com/en-us/library/ms173825.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-17 : 18:25:51

select * from

(select distinct Employer_Ref, client_Number, job_title,
ROW_NUMBER() OVER (PARTITION BY client_number,Job_Title Order By client_Number) as rnum
from Assignments
where Employer_Ref In ('OMWEEK','OWWEEK') and Actual_End = 0)dt

where rnum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Jannette
Starting Member

26 Posts

Posted - 2012-02-20 : 05:12:39

Thankyou malachi151, I have used DENSE_RANK() and it all works perfectly now.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources