Help Getting only one record for multiple values

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

General SQL Server Forums

New to SQL Server Programming

Help Getting only one record for multiple values

Author

Topic

sachin.b
Starting Member

1 Post

Posted - 2011-07-01 : 01:54:53

I have a table where one column values repeat and i want to get only one record with all the fields for the repeating value.
e.g.

UserID XYZ ABC PQRS
116 XXX XXX XXX
116 XXX XXX XXX
116 XXX XXX XXX
117 XXX XXX XXX
117 XXX XXX XXX
117 XXX XXX XXX

now in the above example table i want only one record for
USERID 116 and 117.

Please Help

Thank You

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-07-01 : 02:20:45

select distinct * from tablename

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-01 : 12:16:45

I dont believe the SQL Distinct Operator works with all columns like that.

http://www.w3schools.com/sql/sql_distinct.asp

SELECT Distinct UserID
FROM TableName1
INNER JOIN TableName1
ON UserID = UserID

This Inner Join should yield the other values in your table.

Best of Luck!
Dasman

==========================
Pain is Weakness Leaving the Body.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-03 : 18:06:07

>> I have a table where one column values repeat and i want to get only one record [sic: rows are not records]with all the fields [sic: fields are not columns] for the repeating value [sic: tables do not have repeated rows] <<

The quick answer is "SELECT DISTINCT * FROM Foobar;"

The right answer is your DDL is not a model of a valid RDBMS. Why isn't user_id the PRIMARY KEY? What you have written is a deck of punch cards. If you need a count of duplicate data, then you need a column for that count.

e.g.

UserID XYZ ABC PQRS
116 XXX XXX XXX
116 XXX XXX XXX
116 XXX XXX XXX
117 XXX XXX XXX
117 XXX XXX XXX
117 XXX XXX XXX

now in the above example table i want only one record for
USERID 116 and 117.

Please Help

Thank You

[/quote]

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources