Query Issue producing strange results

Question

Post reply

Query Issue producing strange results

sc-w

Hall of Fame

Points: 3667
More actions
October 13, 2009 at 1:30 am

#212102

Hi, I have query that is giving me results I can't put any logic to on my production environment but when I try and replicate it locally and so I can post it here the results are working fine.
Basically I have a select query that checks a table for delegateid's and displays only one row of a delegateid. A bit more detail, there are 26,000 rows in the production table and a delegate could be in the table many times therefore duplications of the delegateid exists but I just need to display one of those rows for each delegate.
Below is a script that creates a table with four columns and the exact rows i am having the issue with on the server. 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.
Maybe someone can ask some questions to help find out why this is happening.
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 for any help.
**** 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_codevarchar(40) NULL DEFAULT(' '),
delegateidvarchar(40) NULL DEFAULT(' '),
delegate_namevarchar(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')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

is this what you are trying to achieve ?

with cteDelegates(course_code, delegate_name, delegateid ,rown)

as

(

Select course_code, delegate_name, delegateid,ROW_NUMBER() OVER (PARTITION BY DELEGATEID ORDER BY DELEGATEID)

from #wce_course_delegate_link

where course_code = 'AAEM001V3WB'

)

Select * from cteDelegates where rown = 1 order by delegate_name

Clear Sky SQL
My Blog[/url]

sc-w Hall of Fame Points: 3667 More actions · Answer 2

Thanks for the reply, i tried that but it gave me an error pointing at the ) bracket. i then tried the following and this gives me all the results, duplicates included which is what i am trying to get away from.

Select course_code, delegate_name, delegateid, ROW_NUMBER() OVER (PARTITION BY DELEGATEID ORDER BY DELEGATEID)

from wce_course_delegate_link where course_code = 'AAEM001V3WB' order by delegate_name

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Whats the exact error ?,i did change the table to a temp one.

this script runs fine.

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