November 17, 2009 at 11:23 am
Here is some test data with a select statement
--===== If #MyTable already exists, drop it
IF OBJECT_ID('TempDB..#MyTable','U') IS NOT NULL
DROP TABLE #MyTable
--===== Create #MyTable
CREATE TABLE [dbo].[#MyTable](
ResultID [int] IDENTITY(1,1) NOT NULL,
QuestionID [int] NULL,
OptionTextboxValue [varchar](25) NULL,
SurveyResultID [int]NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ResultID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--===== Insert test data into mytable
INSERT INTO #MyTable
(QuestionID, OptionTextboxValue, SurveyResultID)
SELECT '10','80001','1' UNION ALL
SELECT '11','12345','1' UNION ALL
SELECT '12','test1','1' UNION ALL
SELECT '13','n/a','1' UNION ALL
SELECT '20','80002','2' UNION ALL
SELECT '21','678910','2' UNION ALL
SELECT '22','test2','2' UNION ALL
SELECT '23','n/a','2' UNION ALL
SELECT '30','80003','3' UNION ALL
SELECT '31','111213','3' UNION ALL
SELECT '32','test3','3' UNION ALL
SELECT '33','n/a','3'
--=====Return Results
SELECT ResultID ,
QuestionID ,
OptionTextboxValue ,
SurveyResultID
FROM #MyTable
GROUP BY SurveyResultID ,
OptionTextboxValue ,
QuestionID ,
ResultID
ORDER BY ResultID
--====Drop table
DROP TABLE #MyTable
Now this select statement above returns all the rows in that table, what I am interested in is in returning the field OptionTextboxValue; but only the 2nd row of each SurveyResultID. So in this case the only values I wish to see returned are '12345', '678910', '111213'.
I know if did "Where question id like '%1' " would work but the questionid will not always end in '1'. But I do know if I keep the group by and order by the same, this will always be the 2nd row in the resultset. Any help is greatly appreciated.
Thanks in advance
November 17, 2009 at 11:37 am
Try replacing your final select with:
;with CTE as
(SELECT ResultID ,
QuestionID ,
OptionTextboxValue ,
SurveyResultID,
row_number() over (partition by SurveyResultID order by QuestionID) as Row
FROM #MyTable)
select *
from CTE
where Row = 2;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2009 at 12:30 pm
Thats it!! thanks G2.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply