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

distinct columns Expand / Collapse
Author
Message
Posted Saturday, July 17, 2010 12:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
i have a table with no unique fields but and when i use distinct for column1 then it gives 3000 rows or values but when i use distinct for all the fields then it gives 7000 rows. I want all rows related to the column one ie. i want to get distict all rows on basis of coloumn1 ie 3000 rows of all fields.
currently i m useing query :
select distinct * from table1 where column1 in (select distinct column1 from table 1).
but this is giving 7000 rows but dustinct column1 is only 3000 rows.
Post #954310
Posted Saturday, July 17, 2010 2:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 6,824, Visits: 13,271
It's not clear to me what you're looking for.
What rows do you want to return?
Simple scenario:
col1 col2
A1 1
A1 2
A2 3
A2 4
What would be your expected output?




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 #954326
Posted Thursday, October 10, 2013 12:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 12:33 AM
Points: 1, Visits: 1
the question was not cleared....clarify what u want
Post #1503381
Posted Thursday, October 10, 2013 12:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Pretty simple actually.

Distinct column1 will give the distinct values within the column1
Distinct * will give the distinct rows within the table.

Say this is the content of table1

Column1     Column2
1001 A
1001 B
1001 C
1002 A
1002 B
1003 C
1003 C


Select Distinct column1 from table1 will give following result:

Column1
1001
1002
1003


Distinct * from table1 will give following result:

Column1       Column2
1001 A
1001 B
1001 C
1002 A
1002 B
1003 C


As only <1003>,<C> is the row (*) which is duplicate.
Post #1503388
Posted Thursday, October 10, 2013 1:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:10 AM
Points: 1,151, Visits: 82
CREATE TABLE TmpTable (Col1 varchar(10),Col2 int)

SELECT Col1,Col2 FROM TmpTable

Col1 Col2
A 10
A 11
B 12
B 8
B 15
C 18
C 24
C 28
C 35

SELECT DISTINCT Col1 FROM TmpTable

Col1
A
B
C

SELECT DISTINCT Col1,Col2 FROM TmpTable

Col1 Col2
A 10
A 11
B 8
B 12
B 15
C 18
C 24
C 28
C 35

USE BELOW FOR YOUR PURPOSE

SELECT * FROM
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY Col1 ORDER BY Col2 desc ) TCOL
, Col1
, Col2
FROM TmpTable
) A
WHERE TCOL = 1

TCOL Col1 Col2
1 A 11
1 B 15
1 C 35

Modify the query as per your requirement.
Post #1503392
Posted Thursday, October 10, 2013 7:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 3,417, Visits: 5,328
Firstly, in a query as follows there is no need for the second DISTINCT:

select distinct * from table1 where column1 in (select distinct column1 from table 1).


Secondly, you may be able to GROUP BY column1, if the result you want returned for column2 is either the MIN or the MAX value in the rows that match the grouped column.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1503846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse