distinct - 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

General SQL Server Forums

New to SQL Server Programming

distinct

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-07-19 : 11:06:04

I have a table where I want all distinct rows by two rows. So if there are thirty rows that have a field SC, and a field A. I only want to pull one. Here is what I have.

SELECT Distinct territory, state
FROM Territories
where state In ('AL','TN', 'GA', 'SC', 'FL') and companyid = 2

it works with just territory.....but I want state to show up too which will increase results.

Thank you.

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-19 : 11:42:04

quote:
Originally posted by JJins

I have a table where I want all distinct rows by two rows. So if there are thirty rows that have a field SC, and a field A. I only want to pull one. Here is what I have.

SELECT Distinct *
FROM Territories
where state In ('AL','TN', 'GA', 'SC', 'FL') and companyid = 2

it works with just territory.....but I want state to show up too which will increase results.

Thank you.

Not sure if this is the fix but try *

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-19 : 12:22:40

quote:
Originally posted by JJins

I have a table where I want all distinct rows by two rows. So if there are thirty rows that have a field SC, and a field A. I only want to pull one. Here is what I have.

SELECT Distinct territory, state
FROM Territories
where state In ('AL','TN', 'GA', 'SC', 'FL') and companyid = 2

it works with just territory.....but I want state to show up too which will increase results.

Thank you.

I'm really confused by your question. It appears you are mixing fields (columns) with values as well as rows and columns in your problem statement. I can guess at what you might want. However, it'd really help if you demonstrated with actual data and what you want as output. This link will show you how to post that so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-19 : 17:38:54

Fields are not columns. SELECT DISTINCT applies to rows. You did not do your homework :)

--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