How will get distinct records in SQL table?

Question

Post reply

How will get distinct records in SQL table?

sql-programmers

SSC-Addicted

Points: 494
More actions
December 25, 2013 at 10:13 pm

#280634

For examble:
In a table name like
1.Test
2.Test.
3.Test..
4.Test...
How will get the name "Test" using distinct keyword?
SQL Sever Performance tuning

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

SELECT DISTINCT Name

FROM dbo.YourTable

;

But, beware... this type of logic is frequently due to bad table or data design and can lead to some mighty slow queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 2

Would you mind being a little more specific?

What is the "business logic" to get to the result you're looking for?

It's unknown, whether the value is "2.Test." or "Test.".

It is also unknown, whether you're looking for identical characters at position 3 to 7 (or 1 to 4, respectively), or for the substring "Test", regardless of the position within the string itself.

Please help us help you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sql-programmers SSC-Addicted Points: 494 More actions · Answer 3

Thanks for your response..

I need little more specific..

Business logic is I need to export all values from a table without any duplication. I considered Test. Test.. are the duplicates.

The value is "Test." not "2.Test."

SQL Sever Performance tuning

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

sql-programmers (1/1/2014)
Thanks for your response..
I need little more specific..
Business logic is I need to export all values from a table without any duplication. I considered Test. Test.. are the duplicates.
The value is "Test." not "2.Test."

Then your SELECT DISTINCT will do fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 5

I don't think a simple DISTINCT will do it since it would return two rows ("Test." and "Test..") where one ("Test") is expected.

Maybe one of the following approaches will work:

SELECT DISTINCT (LEFT(yourColumn,4)) FROM yourTable

SELECT DISTINCT (REPLACE(yourColumn,'.','')) FROM yourTable

But that's just guessing based on your sample data...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

LutzM (1/2/2014)
I don't think a simple DISTINCT will do it since it would return two rows ("Test." and "Test..") where one ("Test") is expected.
Maybe one of the following approaches will work:
SELECT DISTINCT (LEFT(yourColumn,4)) FROM yourTable
SELECT DISTINCT (REPLACE(yourColumn,'.','')) FROM yourTable
But that's just guessing based on your sample data...

Dang... I thought the dots were just noise. This is why I wish people would provide data in a readily consumable format.

@sql-programmers,

Before you make another post, please see the first link under "Helpful Links" in my signature line below. It helps eliminate confusion and increases the likelihood of you actually getting a correct answer to your problem and fairly quickly, too.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)