Require Query - 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)

Require Query

Author

Topic

KunalDes
Starting Member

4 Posts

Posted - 2009-04-24 : 01:20:26

I have a table with 2 columns (a) Id, (b) Name.
It's data are as under:
-1 John
-2 Jo
-3 Johnn
-4 Kunal
-5 Johny

I want a query which groups records based on first 3 characters of Name Column. So in this case there would be 3 groups (a)John, Johnn, Johny, (b) Jo and (c) Kunal. After grouping I want the topmost record of each group.

So my output would be like :
- John
- Jo
- Kunal.

Please let me know how can I accomplish this. Stored Procedure or joins or anything else. Everything will be fine for me.

Thanking in anticipation,
Kunal

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-04-24 : 01:25:37

[code]
DECLARE @T TABLE (id INT, Names varchar(100))
INSERT INTO @T SELECT
1, 'John' UNION ALL SELECT
2, 'Jo' UNION ALL SELECT
3, 'Johnn' UNION ALL SELECT
4, 'Kunal' UNION ALL SELECT
5, 'Johny'

;WITH CTE AS
(
SELECT Id, Names, ROW_NUMBER()OVER(Partition BY LEFT(Names,3) ORDER BY Id) as Row
FROM @t
)

SELECT Id, Names
FROM Cte
WHERE row=1

[/code]

Mangal Pardeshi
http://mangalpardeshi.blogspot.com