July 17, 2010 at 12:01 am
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.
July 17, 2010 at 2:34 am
It's not clear to me what you're looking for.
What rows do you want to return?
Simple scenario:
col1col2
A11
A12
A23
A24
What would be your expected output?
October 10, 2013 at 12:29 am
the question was not cleared....clarify what u want
October 10, 2013 at 12:53 am
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.
October 10, 2013 at 1:06 am
CREATE TABLE TmpTable (Col1 varchar(10),Col2 int)
SELECT Col1,Col2 FROM TmpTable
Col1Col2
A10
A11
B12
B8
B15
C18
C24
C28
C35
SELECT DISTINCT Col1 FROM TmpTable
Col1
A
B
C
SELECT DISTINCT Col1,Col2 FROM TmpTable
Col1Col2
A10
A11
B8
B12
B15
C18
C24
C28
C35
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
TCOLCol1Col2
1A11
1B15
1C35
Modify the query as per your requirement.
October 10, 2013 at 7:21 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply