Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How will get distinct records in SQL table? Expand / Collapse
Author
Message
Posted Wednesday, December 25, 2013 10:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 11:02 PM
Points: 18, Visits: 274
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
Post #1525933
Posted Thursday, December 26, 2013 5:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
 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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1525946
Posted Saturday, December 28, 2013 5:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1526286
Posted Wednesday, January 01, 2014 11:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 11:02 PM
Points: 18, Visits: 274
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
Post #1526981
Posted Thursday, January 02, 2014 1:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526996
Posted Thursday, January 02, 2014 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1527002
Posted Thursday, January 02, 2014 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse