SELECT get distinct rows using like operator

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 2008 Forums

Transact-SQL (2008)

SELECT get distinct rows using like operator

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-03-01 : 11:14:22

How can i get distinct type_id, the query is getting almost 20k rows, most of them are duplicate type_id rows.

I am doing a keyword search.

select id,ocdata,tiffile,type,type_id from TAB_OcrData where ocdata LIKE ''%'+@KeyWord+'%''

Thank you very much for the helpful info.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-03-01 : 11:25:02

I did the following using distinct with type_id , i am getting duplicate type_id rows in the resultset:

select distinct type_id,ocdata,tiffile,type,id from TAB_OcrData where ocdata LIKE ''%'+@KeyWord+'%''

Thank you very much for the helpful info.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-01 : 11:35:53

You need to decide which row you want.
The following selects the row with the highest id for each type_id.


;WITH dRN
AS
(
	SELECT [type_id], ocdata, tiffile, [type], id
		,ROW_NUMBER() OVER (PARTITION BY [type_id] ORDER BY id DESC) AS RN
	FROM TAB_OcrData
	WHERE ocdata LIKE '%' + @KeyWord + '%'
)
SELECT [type_id], ocdata, tiffile, [type], id
FROM dRN
WHERE RN = 1

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-03-01 : 11:37:22

type_id is impt, id i can remove from the select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources