Get a subset or group of data based on ID and only the first "group"

Transact-SQL
1

A little difficult to explain but here goes.

CREATE TABLE [dbo].[SurveyCourse](
_ [ID] [int] IDENTITY(1,1) NOT NULL,_
_ [CourseID] [int] NOT NULL,_
_ [SurveyID] [int] NOT NULL,_
_ [MasterSurveyID] [int] NOT NULL,_
_ CONSTRAINT [PK_SurveyCourse] PRIMARY KEY CLUSTERED _
(
_ [ID] ASC_
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO SurveyCourse VALUES (2, 101, 3)
INSERT INTO SurveyCourse VALUES (2, 102, 3)
INSERT INTO SurveyCourse VALUES (2, 103, 3)
INSERT INTO SurveyCourse VALUES (2, 104, 3)

INSERT INTO SurveyCourse VALUES (5, 101, 3)
INSERT INTO SurveyCourse VALUES (5, 102, 3)
INSERT INTO SurveyCourse VALUES (5, 103, 3)
INSERT INTO SurveyCourse VALUES (5, 104, 3)

INSERT INTO SurveyCourse VALUES (8, 101, 4)
INSERT INTO SurveyCourse VALUES (8, 102, 4)
INSERT INTO SurveyCourse VALUES (8, 103, 4)
INSERT INTO SurveyCourse VALUES (8, 104, 4)

What I want is a query where, for a given MasterSurveyID, I want to retrieve just the first group of data (i.e only the CourseID where the MasterSurveyID is 3 but instead of giving me all records where Course ID is 2 and 5, JUST the first group which is where courseID is 5)

How can I achieve this?

using SQL Server 2008R2

2

Can't tell how you decided that courseID 5 was the "first group of data".

3

Thanks for your speedy reply @ScottPletcher

That's the thing. Is there a way to "split" the groupings so continue to get rows until the CourseID is different and then "Stop" grabbing the records? Then the ones we "grabbed" would be the group I want returned.

4

Could this be what you're looking for

Query 
select top(1) with ties
       *
  from dbo.surveycourse
 where mastersurveyid=3
 order by rank() over(order by courseid desc)
;
1 Like
5

Yes! Thank you.