query removes rows of same combination of cols

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query removes rows of same combination of cols

Author

Topic

crishna
Starting Member

5 Posts

Posted - 2008-10-29 : 15:06:18

Hi,

I need to write a query

Attached is the Image wich gives look of how my data looks now and what I need it look like with the query

Could you please help me with a query?

Row Column LabelText TextBoxLabelText CheckBoxText FormCellType
F7A8AE42 9C790FB2 Command and Display Label
F7A8AE42 B34B7D87 Command and Display Label
F7A8AE42 FFFFFFFFFF Check Engine Text Field
EDE01A0E 9C790FB2 Job Name TextField
EDE01A0E B34B7D87 Job Name TextField
EDE01A0E GGGGGGG Command label
DAC812FC 9C790FB2 Job Number TextField
DAC812FC B34B7D87 Job Number TextField
DAC812FC 8AF95DDC Job Number TextField

Desired Result
Row Column LabelText TextBoxLabelText CheckBoxText FormCellType
F7A8AE42 9C790FB2 Command and Display Label
F7A8AE42 FFFFFFFF Check Engine Text Field
EDE01A0E 9C790FB2 Job Name TextField
EDE01A0E GGGGGGG Command label
DAC812FC 9C790FB2 Job Number TextField

Could any help me in writing query for above.

Thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-29 : 15:08:48

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113262&SearchTerms=remove,duplicate

crishna
Starting Member

5 Posts

Posted - 2008-10-29 : 15:19:47

Thanks for the reply,

I looked at the post and I did use a similar query using Group By which return me the result, but it doesnt retunr me the
first two columns I need.

I am trying to retreive even the corresponding RowId and ColumnId also.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-29 : 15:21:28

Maybe it's not a bad idea to show your query so far...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-29 : 18:05:25

[code]DECLARE @Sample TABLE
(
Row CHAR(8),
[Column] VARCHAR(10),
LabelText VARCHAR(20),
TextBoxLabelText VARCHAR(20),
CheckBoxText VARCHAR(20),
FormCellType VARCHAR(9)
)

INSERT @Sample
SELECT 'F7A8AE42', '9C790FB2', 'Command and Display', NULL, NULL, 'Label' UNION ALL
SELECT 'F7A8AE42', 'B34B7D87', 'Command and Display', NULL, NULL, 'Label' UNION ALL
SELECT 'F7A8AE42', 'FFFFFFFFFF', NULL, 'Check Engine', NULL, 'TextField' UNION ALL
SELECT 'EDE01A0E', '9C790FB2', NULL, 'Job Name', NULL, 'TextField' UNION ALL
SELECT 'EDE01A0E', 'B34B7D87', NULL, 'Job Name', NULL, 'TextField' UNION ALL
SELECT 'EDE01A0E', 'GGGGGGG', 'Command', NULL, NULL, 'Label' UNION ALL
SELECT 'DAC812FC', '9C790FB2', NULL, 'Job Number', NULL, 'TextField' UNION ALL
SELECT 'DAC812FC', 'B34B7D87', NULL, 'Job Number', NULL, 'TextField' UNION ALL
SELECT 'DAC812FC', '8AF95DDC', NULL, 'Job Number', NULL, 'TextField'

SELECT Row,
[Column],
LabelText,
TextBoxLabelText,
CheckBoxText,
FormCellType
FROM (
SELECT Row,
[Column],
LabelText,
TextBoxLabelText,
CheckBoxText,
FormCellType,
ROW_NUMBER() OVER (PARTITION BY Row, LabelText, TextBoxLabelText, CheckBoxText, FormCellType ORDER BY Row) AS recID
FROM @Sample
) AS d
WHERE recID = 1
ORDER BY Row DESC,
[Column]

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Row, LabelText, TextBoxLabelText, CheckBoxText, FormCellType ORDER BY Row) AS recID
FROM @Sample
) AS f
WHERE recID > 1

SELECT Row,
[Column],
LabelText,
TextBoxLabelText,
CheckBoxText,
FormCellType
FROM @Sample
ORDER BY Row DESC,
[Column][/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources