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

SQL Server 2005 Forums

Transact-SQL (2005)

disctinct

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-17 : 01:04:09

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.

scoo

Sachin.Nand

2937 Posts

Posted - 2010-07-17 : 02:11:59

I bet the count of the records in your table is 7000.Change your In query to this


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

It should give 3000 records.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-17 : 02:49:37

ok but this is for distict collumn and not for on the basis of all rows.
ie i want 3000 rows of all fields to be displayed on basis of distinct column1.
and yes total records are 7000.

scoo

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-17 : 02:51:17

ok but this is for distict collumn and not for on the basis of all rows.
ie i want 3000 rows of all fields to be displayed on basis of distinct column1.
and yes total records are 7000.

quote:
Originally posted by Idera

I bet the count of the records in your table is 7000.Change your In query to this

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

It should give 3000 records.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

scoo

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 02:53:50

So, for a given value in Column1 - where there are several rows (with different data) for that value, which row do you want to choose the other columns from?

This request is commonly "I want to see the most recent row for each value in Column1 - where the "ChangeDate" column contains the appropriate date"

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 03:00:07

Here's an example which you can adapt to your columns


SELECT	*
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY Column1
				ORDER BY Column1, ChangeDate DESC
			),
		*
	FROM	MyTable
) AS T
WHERE	T_RowNumber = 1
ORDER BY Column1

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-17 : 03:25:58

all want to get all 3000 rows wich was distinct for column1 and with that all rows. so that result ll be 3000 rows with all fields.

quote:
Originally posted by Kristen

So, for a given value in Column1 - where there are several rows (with different data) for that value, which row do you want to choose the other columns from?

This request is commonly "I want to see the most recent row for each value in Column1 - where the "ChangeDate" column contains the appropriate date"

scoo

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 04:02:24

You have 7000 rows, and 3000 distinct values for Column1

You want to display 3000 rows out of the 7000 - which 5000 do you NOT want to display?

perhaps if you provide an example of some rows, which have the same value for Column1, and what you want as the output that would help us understand

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-17 : 05:32:13

for example:
column1 col2 col3 col4
1 a b c
1 a c b
2 a c b
2 c b a
1 c a a
3 a c b

now when i fetch select distinct column1 ffrom table it gives me 3 distinct rows acording to column number . and there r 6 records in table.
and when i write select distinct * from table where column1 in (select distinct column from table) it gives all this 6 rows..
so i want three rows but all the column but on the basis of column number. ex:
hope now you can understand

quote:
Originally posted by Kristen

You have 7000 rows, and 3000 distinct values for Column1

You want to display 3000 rows out of the 7000 - which 5000 do you NOT want to display?

perhaps if you provide an example of some rows, which have the same value for Column1, and what you want as the output that would help us understand

scoo

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 05:36:33

Please provide example of the output you want

1 ???
2 ???
3 A C B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources