April 2, 2009 at 12:53 am
Hi,
I have a doubt here, i want to remove the rows if my result set (QText) column has return NULL value in a table. Instead of 4 rows, i want to get only one row. How can i archive this.
My result set should be:
-----------------------
QText Atext
------ -----
Test 1
CREATE TABLE tblQuestion (Qid int, QText varchar(20))
CREATE TABLE tblAnswer (Aid int, AText varchar(20))
INSERT INTO tblQuestion VALUES(1,'Test')
INSERT INTO tblAnswer VALUES(1,'1')
INSERT INTO tblAnswer VALUES(1,'2')
INSERT INTO tblAnswer VALUES(1,'3')
INSERT INTO tblAnswer VALUES(1,'4')
SELECT
CASE WHEN (Row_Number() OVER (ORDER BY Q.QText)) > 1 THEN NULL
ELSE Q.QText END AS QText,
A.Atext
FROM tblQuestion AS Q
INNER JOIN tblAnswer AS A
ON (Q.Qid = A.Aid)
Appreciated your help!!! 🙂
---
April 2, 2009 at 1:14 am
If you can provide us the required output for the sample you have given, we can provide you with better solution. If you just wanted to filter rows then apply a where clause to the query. for e.g.
SELECT
CASE WHEN (Row_Number() OVER (ORDER BY Q.QText)) > 1 THEN NULL
ELSE Q.QText END AS QText,
A.Atext
FROM tblQuestion AS Q
INNER JOIN tblAnswer AS A
ON (Q.Qid = A.Aid)
WHERE Q.QText IS NULL
--Ramesh
April 2, 2009 at 1:39 am
You can try the below query as well.....There will be a better way...will check and revert back..
SELECT * FROM
(
SELECT
CASE WHEN (Row_Number() OVER (ORDER BY Q.QText)) > 1 THEN NULL
ELSE Q.QText END AS QText,
A.Atext
FROM tblQuestion AS Q
INNER JOIN tblAnswer AS A ON (Q.Qid = A.Aid)
) QA
WHERE QText IS NOT NULL
April 2, 2009 at 3:53 am
San (4/2/2009)
You can try the below query as well.....There will be a better way...will check and revert back..SELECT * FROM
(
SELECT
CASE WHEN (Row_Number() OVER (ORDER BY Q.QText)) > 1 THEN NULL
ELSE Q.QText END AS QText,
A.Atext
FROM tblQuestion AS Q
INNER JOIN tblAnswer AS A ON (Q.Qid = A.Aid)
) QA
WHERE QText IS NOT NULL
Yes, it works fine. tks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply