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 2005 Forums
 Transact-SQL (2005)
 Query Question

Author  Topic 

mshsilver
Posting Yak Master

112 Posts
Posted - 2009-10-12 : 06:01:19
Hi,

I thought i had a query that checks through a table and where it finds a course_code that is duplicated it will only display one row per course.

Here it is:


SELECT uniqueid, delegate_name, delegate_ni_no, delegate_phone,delegate_mobilephone, delegateid, delegate_emailaddress, delegate_sex, delegate_disability, Delegate_Learning_Difficulty, Delegate_Learning_Diff, recordmanager, entityid FROM (SELECT ROW_NUMBER() OVER (PARTITION BY delegate_name ORDER BY delegate_name) r, * FROM  wce_course_delegate_link) A WHERE r = 1 AND course_code = '10008123-R'


As i mentioned, i thought it worked... I came across a student that did not have a course that should have so i took the course code and run simple query to check and this simple query brings me one row.


select * from wce_course_delegate_link where course_code = '10008123-R'



Is it possible that my first query is only showing rows where it see's more than 2 course duplicate course_code's and where it see's one the query breaks?

WoodHouse
Posting Yak Master

211 Posts
Posted - 2009-10-12 : 06:08:10
Hi

Formatted Query...

SELECT uniqueid                    ,
       delegate_name               ,
       delegate_ni_no              ,
       delegate_phone              ,
       delegate_mobilephone        ,
       delegateid                  ,
       delegate_emailaddress       ,
       delegate_sex                ,
       delegate_disability         ,
       Delegate_Learning_Difficulty,
       Delegate_Learning_Diff      ,
       recordmanager               ,
       entityid
FROM
       (SELECT  ROW_NUMBER() OVER (PARTITION BY delegate_name ORDER BY delegate_name) r,
                *
       FROM     wce_course_delegate_link
       ) A
WHERE  r           = 1
AND    course_code = '10008123-R
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-10-12 : 06:13:41
No.

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

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-10-12 : 06:18:41
But - your student with delegate_name 'Smith' for example can have more than one course.
Your row_number() does not consider any courses.
So your select is just returning 1 row for each delegate_name.

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

mshsilver
Posting Yak Master

112 Posts
Posted - 2009-10-12 : 06:19:17
Sorry i am not understanding. This should be giving me one row but comes up with none.



SELECT uniqueid                    ,
       delegate_name               ,
       delegate_ni_no              ,
       delegate_phone              ,
       delegate_mobilephone        ,
       delegateid                  ,
       delegate_emailaddress       ,
       delegate_sex                ,
       delegate_disability         ,
       Delegate_Learning_Difficulty,
       Delegate_Learning_Diff      ,
       recordmanager               ,
       entityid
FROM
       (SELECT  ROW_NUMBER() OVER (PARTITION BY delegate_name ORDER BY delegate_name) r,
                *
       FROM     wce_course_delegate_link
       ) A
WHERE  r           = 1
AND    course_code = '10008123-R'
Go to Top of Page

mshsilver
Posting Yak Master

112 Posts
Posted - 2009-10-12 : 06:28:42
Go it, great thank you for your help
Go to Top of Page

sanoj_av
Posting Yak Master

118 Posts
Posted - 2009-10-12 : 06:38:11
what is the result u get when querying
SELECT delegate_name FROM wce_course_delegate_link WHERE course_code = '10008123-R'
Go to Top of Page

mshsilver
Posting Yak Master

112 Posts
Posted - 2009-10-12 : 08:49:09
Hi Again, i still have an issue but need to put some sample code up so you guys can replicate what i am seeing. I can't remember or find the way of scripting the following so i can insert 60+ static rows. One row works fine but you need all the sample data to see what i'm seeing. Just to recap, this creates a table and if i only have one row of values it inserts fine, i just can't firgure out the method to insert more than one.

Any help would be great?



CREATE TABLE STEPHCRM (


uniqueid 	int IDENTITY(1,1) NOT NULL,
course_code	varchar(40) NULL DEFAULT(' '),
delegateid	varchar(40) NULL DEFAULT(' '),
delegate_name	varchar(40) NULL DEFAULT(' ')

)


INSERT INTO STEPHCRM (course_code, delegate_name, delegateid) values  
('AAEM001V3WB', 'Adam Bourne',	'77002333')
('AAEM032REG', 'Adam JONES',	'77002324')
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-10-12 : 09:47:30
INSERT INTO STEPHCRM (course_code, delegate_name, delegateid)
select 'AAEM001V3WB', 'Adam Bourne', '77002333' union all
select 'AAEM032REG', 'Adam JONES', '77002324'

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

mshsilver
Posting Yak Master

112 Posts
Posted - 2009-10-12 : 10:15:19
Thanks, I just got it inserting.

Ok, this problem I thought I was going to be able to demonstrate but on my local machine this is doing what I expect it to do and giving me the results I need on the actual server.

It removes the duplicates where a delegateid exist more than once and then displays the results. On the actual server this is not working and I am getting unexplainable results. Maybe someone can ask the right questions once you have this script setup locally too.

The only things that stand out between the two environments are that the server has 26000 rows of data in wce_course_delegate_link and the local test only the 67. On the server there are 2086 unique course_code’s and here we only have 1 which shouldn’t matter.

Here are my select commands, run these after you have created the test table and inserted the dummy rows.

You will the first query (A) removes the duplicates and produce 60 rows. And the second query (B) showing all, produces 67 rows 7 of which are duplicated records.

When I do the same queries on the server (A) gives me 21 rows and (B) 67… My head is mush now, any input would be great. Thanks


**** SELECT QUERIES


--QUERY A
SELECT  course_code, delegate_name, delegateid FROM (SELECT ROW_NUMBER() OVER (PARTITION BY DELEGATEID ORDER BY DELEGATEID) r, * FROM  wce_course_delegate_link) A WHERE r = 1 AND course_code = 'AAEM001V3WB' order by delegate_name

--QUERY B
select course_code, delegate_name, delegateid from wce_course_delegate_link where course_code = 'AAEM001V3WB' order by delegate_name





*****SAMPLE CODE TO CREATE TABLE AND INSERT DATA*****




IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'WCE_COURSE_DELEGATE_LINK')
DROP TABLE WCE_COURSE_DELEGATE_LINK
GO

CREATE TABLE WCE_COURSE_DELEGATE_LINK (


uniqueid 	int IDENTITY(1,1) NOT NULL,
course_code	varchar(40) NULL DEFAULT(' '),
delegateid	varchar(40) NULL DEFAULT(' '),
delegate_name	varchar(40) NULL DEFAULT(' ')
)
GO

INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values  

('AAEM001V3WB',         	'Adam Bourne',	'77002333')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Alex Rowland',	'71031345')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Ammar Khan',	'77002955')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Andrew Connelly',	'77008900')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Andrew Hunt',	'77002859')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Ashley Oakey',	'77003212')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Ben Lovegrove',	'08017243')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Ben Riddle',	'08017260')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Bradley Wood',	'09014129')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Craig Matthews',	'09017045')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Curt Buckingham',	'73046920')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Curt Buckingham',	'73046920')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Daniel Ogden',	'09014833')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Daniel Watson',	'08005543')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Danielle Patenall',	'08017250')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Declan Turner',	'77099025')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Declan Turner',	'77099025')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Declan Turner',	'77099025')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Dwayne Abbott',	'77002200')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Gary Szejok',	'76096207')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Graeme Cram',	'77002503')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Graeme Cram',	'77002503')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Greg Haigh',	'77002730')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Henry Pickering',	'76096029')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Jadan Walton',	'77003641')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'James Aldrich',	'77001276')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'James Brooks',	'76076339')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'James Brooks',	'76076339')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Joe Upton',	'77001695')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Jojo Underwood',	'08017244')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Jonathan Peat',	'75074005')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Jonathan Peat',	'75074005')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Kyle Holland',	'76077640')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Leighton West',	'08005546')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Lewis English',	'08017246')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Liam Holland',	'75075512')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Luke Benson',	'77002294')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Luke Dutton',	'08008002')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Luke Shepherd',	'77003419')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Mark Hartley',	'08017220')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Matthew Brough',	'08005355')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Matthew Jackson',	'77002872')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Matthew Walker',	'75074927')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Michael Dixon',	'08017212')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Michael Taylor',	'77003556')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Morgan Salt',	'76096104')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Morten Ellis',	'77002605')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Morten Ellis',	'77002605')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Nathaniel Hart',	'08010424')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Nikki Jannaway',	'77002882')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Paul Morren',	'08017231')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Paul Sweeney',	'77003537')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Rebecca Shaw',	'08010421')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Richard Pepperdine',	'77003267')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Richard Roberts',	'08017254')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Ricky Gregory',	'74056864')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Robert Potter',	'76087168')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Scott Atkinson',	'08005333')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Scott Smith',	'74061469')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Sean Jordan',	'74061694')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Stacy Lee',	'08017237')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Stefan Williams',	'77003696')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Stuart Ford',	'77002647')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Thomas Horne',	'08007744')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Tom Hull',	'77002851')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'Tom Semark',	'77003403')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values 
('AAEM001V3WB',         	'William Measures',	'75075572')
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-10-12 : 10:49:12
In your example Curt Buckingham can have same delegate_name and delegateid but different course_code like this:

course_code             delegate_name           delegateid
'AAEM001V3WB',         	'Curt Buckingham',	'73046920'
'BBEM002V3ZZ',         	'Curt Buckingham',	'73046920'

Your Row_Number() does not consider the course_code so the result can be:

course_code             delegate_name           delegateid   rownumber
'AAEM001V3WB',         	'Curt Buckingham',	'73046920'      1
'BBEM002V3ZZ',         	'Curt Buckingham',	'73046920'      2

Your select says: where rownumber = 1 so 'BBEM002V3ZZ' never could be returned.

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

mshsilver
Posting Yak Master

112 Posts
Posted - 2009-10-12 : 11:18:10
So what you are saying is the other columns are not relevant to the query right? I just proved this by adding to the above example a new row below. Then I run Select query (A) and got the same results as before I added the new row. This is then what I need, it's frustrating not being able to demonstrate the server side results, I am sure there is simple logic behind it.

Thanks for looking



GO

INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values  

('CCCDDDCCC',         	'Adam Bourne',	'77002333')
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site