No repeatetion of names?

Question

Post reply

No repeatetion of names?

alishaik001

SSC Rookie

Points: 40
More actions
December 21, 2011 at 5:00 am

#252384

In my table I have names like"
sqlserver
sqlserver
sqlserver
server
server
sql
sql
sql
sql
abcde
and in my output I want all names and repeated names in only one time;
like:
sqlserver
server
sql
abcde

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLSACT SSC-Insane Points: 21773 More actions · Answer 1

SQLSACT

SSC-Insane

Points: 21773

December 21, 2011 at 5:02 am

#1424139

SELECT DISTINCT

alishaik001 SSC Rookie Points: 40 More actions · Answer 2

I get this by the following query:

SELECT Column_Name FROM TableName GROUP BY Column_Name HAVING ( COUNT(Column_Name) >= 1 );

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

alishaik001 (12/21/2011)
I get this by the following query:
SELECT Column_Name FROM TableName GROUP BY Column_Name HAVING ( COUNT(Column_Name) >= 1 );

Can you post the actual query you are using? The query above, as it stands, would eliminate dupes.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLSACT SSC-Insane Points: 21773 More actions · Answer 4

alishaik001 (12/21/2011)
I get this by the following query:
SELECT Column_Name FROM TableName GROUP BY Column_Name HAVING ( COUNT(Column_Name) >= 1 );

Inbetween SELECT and Column_Name add the keyword DISTINCT - That should eliminate duplicates from the output.

alishaik001 SSC Rookie Points: 40 More actions · Answer 5

My actual query is:

here Name is the fieldName and Employee is the TableName;

SELECT Name FROM Employee GROUP BY Name HAVING ( COUNT(Name) >= 1 );

From this query I got the solution.

Is there any wrong here;

SQLSACT SSC-Insane Points: 21773 More actions · Answer 6

alishaik001 (12/21/2011)
My actual query is:
here Name is the fieldName and Employee is the TableName;
SELECT Name FROM Employee GROUP BY Name HAVING ( COUNT(Name) >= 1 );
From this query I got the solution.
Is there any wrong here;

Try this

SELECT DISTINCT Name FROM Employee GROUP BY Name HAVING ( COUNT(Name) >= 1 );

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

alishaik001 (12/21/2011)
My actual query is:
here Name is the fieldName and Employee is the TableName;
SELECT Name FROM Employee GROUP BY Name HAVING ( COUNT(Name) >= 1 );
From this query I got the solution.
Is there any wrong here;

Translation problem 🙂

Is this your solution or the query you use to obtain the list from your first post?

SELECT Column_Name

FROM (

SELECT Column_Name = 'sqlserver' UNION ALL

SELECT 'sqlserver' UNION ALL

SELECT 'server' UNION ALL

SELECT 'sql' UNION ALL

SELECT 'abcde'

) TableName

GROUP BY Column_Name HAVING ( COUNT(Column_Name) >= 1 )

-- results:

-- abcde

-- server

-- sql

-- sqlserver

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

JonFox SSCrazy Points: 2157 More actions · Answer 8

I'm puzzled about the inclusion of the HAVING ( COUNT(Column_Name) >= 1 ) bit. Every value that exists in Column_Name will have one or more occurrences, so this doesn't do anything except add a few extra operators to the query plan...you'll get the exact same result set if you leave the HAVING clause off altogether.

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 9

There is no significant difference in execution plans between GROUP BY and DISTINCT. Each produces a list of unique values, DISTINCT just doesn't require that aggregations be taken at the same time.

Jon is right, the HAVING clause is not needed in this instance.

Either of the following would produce a de-duped list of column names

SELECT DISTINCT Column_Name

FROM (

SELECT Column_Name = 'sqlserver' UNION ALL

SELECT 'sqlserver' UNION ALL

SELECT 'server' UNION ALL

SELECT 'sql' UNION ALL

SELECT 'abcde'

) TableName

SELECT Column_Name,COUNT(*)

FROM (

SELECT Column_Name = 'sqlserver' UNION ALL

SELECT 'sqlserver' UNION ALL

SELECT 'server' UNION ALL

SELECT 'sql' UNION ALL

SELECT 'abcde'

) TableName

GROUP BY Column_Name

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills