Distinct not working - SQL Server Forums

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)

Distinct not working

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2010-12-26 : 18:41:34

Hi all,

I got this SP

SELECT DISTINCT TOP (20) REPLACE(Searchword, 'S_', '') AS Searchword, id
FROM (SELECT DISTINCT TOP (100) PERCENT Searchword, id
FROM T_sys_searches
WHERE (recordsfound > 0) AND (lang = @lang)
ORDER BY id DESC) AS derivedtbl_1
ORDER BY id DESC

i want Distinct searchwords, but doesn't work because of the ID (which is autonumber field from the table, and i need to find the last 20 added)

i can i do this?

Thanks a lot

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-26 : 20:27:10

SELECT TOP (20) Searchword, id
FROM
(
SELECT Searchword = REPLACE(Searchword, 'S_', ''), id = max(id)
FROM T_sys_searches
WHERE (recordsfound > 0) AND (lang = @lang)
group by REPLACE(Searchword, 'S_', '')
) AS derivedtbl_1
ORDER BY id DESC

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike13
Posting Yak Master

219 Posts

Posted - 2010-12-26 : 20:52:40

Thanks a lot, that did the Trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources