April 13, 2011 at 4:31 pm
-- create table query:
CREATE TABLE mytable (
School varchar(160),
Student varchar(160),
SourceName varchar(12),
SourceType int
);
-- insert table query:
INSERT INTO mytable VALUES('Sunnyville','Neil','RFR','5');
INSERT INTO mytable VALUES('Sunnyville','Patricia','RFR','5');
INSERT INTO mytable VALUES('Sunnyville','Neil','PPC','1');
INSERT INTO mytable VALUES('Sunnyville','Patricia','PPC','1');
INSERT INTO mytable VALUES('Wildleaf','Andrew','PFMG','2');
INSERT INTO mytable VALUES('Wildleaf','Andrew','PMG','3');
INSERT INTO mytable VALUES('Wildleaf','Andrew','RFR','5');
INSERT INTO mytable VALUES('Wildleaf','Andrew','Open Day','6');
INSERT INTO mytable VALUES('Delta','Don','RFR','5');
INSERT INTO mytable VALUES('Franco','Frank','Open Day','6');
INSERT INTO mytable VALUES('Wildleaf','Andrew','CF','4');
INSERT INTO mytable VALUES('Manor','Quinton','PFMG','2');
INSERT INTO mytable VALUES('PawPaw','Stephen','PMG','3');
INSERT INTO mytable VALUES('Pilbury','Andrew','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Brendon','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Carl','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Craig','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Duncan','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Edward','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Ernest','PPC','1');
INSERT INTO mytable VALUES('Pilbury','Freddy','PPC','1');
INSERT INTO mytable VALUES('Wildleaf','Andrew','PPC','1');
-- select query:
SELECT
[School]
,[Student]
,[SourceName]
,[SourceType]
FROM [CRMReports].[dbo].[mytable]
ORDER BY [School],[SourceType]
-- Example 1:
-- where there is multiple SourceType for a Student,
-- then only return rows where SourceType is minimum value, i.e. 1, exclude 2-6.
SchoolStudentSourceNameSourceType
WildleafAndrewPPC1
WildleafAndrewPFMG2
WildleafAndrewPMG3
WildleafAndrewCF4
WildleafAndrewRFR5
WildleafAndrewOpen Day6
-- Example 2:
-- where there is multiple SourceType for a Student,
-- then only return rows where SourceType is minimum value, i.e. 1, exclude 5.
SchoolStudentSourceNameSourceType
SunnyvilleNeilPPC1
SunnyvillePatriciaPPC1
SunnyvilleNeilRFR5
SunnyvillePatriciaRFR5
-- Example 3:
-- where there is only one SourceType for a Student, ignore.
SchoolStudentSourceNameSourceType
DeltaDonRFR5
-- Above info is also available in text file I uploaded called myquery.txt
April 13, 2011 at 5:02 pm
I'm not sure exactly what you want, but this will get you only the rows with the lowest sourcetype and when the student has more than one entry in the table. Modify as you will or discard if I'm way off base.
-- select query:
;
WITH cteStudentOrder
AS (
SELECT
[School],
[Student],
[SourceName],
[SourceType],
ROW_NUMBER() OVER (PARTITION BY student ORDER BY sourcetype) AS row,
COUNT(school) OVER (PARTITION BY student) AS no_rows
FROM
[dbo].[mytable] AS MT
)
SELECT
*
FROM
cteStudentOrder
WHERE
cteStudentOrder.row = 1 AND
cteStudentOrder.no_rows > 1
ORDER BY
school,
student
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2011 at 2:36 pm
Objective: where there is multiple SourceType for a Student, only return rows where SourceType is
minimum value. Thanks Jack, your query worked like a charm.
Had to modify your query a slight bit since even though I ignore incidents where there is only one SourceType for student, and filter out multiple source types for a student and only keep minimum value, I still need to display those incidents where there is only one SourceType for student in my report.
April 16, 2011 at 2:46 pm
Hi Jack, in some of the rows the value for [Student] column is NULL.
This causes your query to give incorrect results. How do I fix ?
April 18, 2011 at 2:08 pm
Thanks Joe, it worked fine.
I modified your code slightly to cater for instances where the student_name is null by using
OVER (PARTITION BY school_name) instead of OVER (PARTITION BY student_name).
Code follows below:
---------------------
SELECT X.*
FROM (SELECT school_name, student_name, source_name, source_type
MIN(source_type)
OVER (PARTITION BY school_name) AS source_type_min
FROM SchoolRecruitment ) AS X
WHERE source_type = source_type_min;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply