CODEWITHSUNDEEP
sql-server
user2487501
user2487501

Reputation: 1

group by query in sql server returns duplicates

i have a problem in Sql server, Group by query which returns me one field of table twice (means duplicate). i don't know how to overcome this problem please.

ALTER PROCEDURE [dbo].[States_of_Path_SelectAll]
AS
    SELECT FromState
    FROM RawPaths
    GROUP BY FromState

Above is the query and it returns a FromState having value equal to Close By twice while other FromState are ok.

output of the query is : Meeting CloseBy Ignore Follow CloseBy

Upvotes: 0

Views: 231

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

You probably have spaces in your data that don't belong. You have a few options:

1) Periodicatly clean up spaces that appear in your data. You can use a statement like this:

update RawPaths
set FromState = ltrim(rtrim(FromState)

2) Have your query remove the spaces:

select ltrim(rtrim(FromState)) as FromState
from RawPaths
group by ltrim(rtrim(FromState))

3) Ensure only valid data can get into your table in the first place (best option).

You could create another table called States with an ID field and a description field. Then change your FromState field to an integer and only allow valid states to be entered into the field though your interface and/or by using key constraints.

Upvotes: 3

Related Questions