Select DISTINCT, return the whole line

Question

I have a table with 10 columns. I want to return all rows for which Col006 is different, but return all columns ...

How can i do this?

if column 6 is as follows:

| Column 6 | | item1 | | item1 | | item2 | | item1 | 

I want to return two rows, one of the records with item1 and the other with item2 along with all the other columns.

Answer 1

In SQL Server 2005 and later:

 ;WITH q AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY col6 ORDER BY id) rn FROM mytable ) SELECT * FROM q WHERE rn = 1 

In SQL Server 2000 , if you have a primary key column:

 SELECT mt.* FROM ( SELECT DISTINCT col6 FROM mytable ) mto JOIN mytable mt ON mt.id = ( SELECT TOP 1 id FROM mytable mti WHERE mti.col6 = mto.col6 -- ORDER BY -- id -- Uncomment the lines above if the order matters ) 

Update:

Check your database version and compatibility level:

 SELECT @@VERSION SELECT COMPATIBILITY_LEVEL FROM sys.databases WHERE name = DB_NAME() 

Answer 2

The keyword "DISTINCT" in SQL has the value "unique value". When applied to a column in a query, it returns as many rows from the result set as there are unique, different values ​​for this column. As a result, it creates a grouped result set, and the values ​​of other columns are random if they are not defined by other functions (such as max, min, average, etc.).

If you want to say that you want to return all rows for which Col006 has a specific value, use the clause "where Col006 = value".

If you want to say that you want to return all rows for which Col006 is different from all other Col006 values, you still need to indicate that it is value => see above.

If you want to say that the value of Col006 can only be evaluated after all the rows have been extracted, use the "Col006 = value" clause. This has the same effect as the where clause, but where applies when rows are retrieved from raw tables, while presence is applied after all other calculations have been performed (i.e., aggregation functions, etc. ), and just before the result set is returned to the user.

UPDATE:

After viewing your edit, I should indicate that if you use any other sentences, you will get random values ​​in all the other nine columns for the row that contains the value “item1” in Col006, due to the restriction further in my post.

Answer 3

You can group Col006 to get different values, but then you need to decide what to do with several records in each group.

You can use aggregates to select values ​​from records. Example:

 select Col006, min(Col001), max(Col002) from TheTable group by Col006 order by Col006 

If you want the values ​​to come from a specific record in each group, you need to identify it somehow. An example of using Col002 to identify an entry in each group:

 select Col006, Col001, Col002 from TheTable t inner join ( select Col006, min(Col002) from TheTable group by Col006 ) x on t.Col006 = x.Col006 and t.Col002 = x.Col002 order by Col006 

Answer 4

 SELECT * FROM (SELECT DISTINCT YourDistinctField FROM YourTable) AS A CROSS APPLY ( SELECT TOP 1 * FROM YourTable B WHERE B.YourDistinctField = A.YourDistinctField ) AS NewTableName 

I tried the answers laid out above, no luck ... but that does the trick!

Answer 5

 select * from yourTable where column6 in (select distinct column6 from yourTable); 

Answer 6

You can use GROUP BY and MIN() to get the result.

Suppose you have id as primary_key . And we want to get all the DISTINCT values ​​for the column, say estimated_total , and you also need one sample full row with each individual value in SQL . The following query should do the trick.

 SELECT *, min(id) FROM harvest GROUP BY estimated_total; 

Answer 7

 create table #temp (C1 TINYINT, C2 TINYINT, C3 TINYINT, C4 TINYINT, C5 TINYINT, C6 TINYINT) INSERT INTO #temp SELECT 1,1,1,1,1,6 UNION ALL SELECT 1,1,1,1,1,6 UNION ALL SELECT 3,1,1,1,1,3 UNION ALL SELECT 4,2,1,1,1,6 SELECT * FROM #temp SELECT * FROM( SELECT ROW_NUMBER() OVER (PARTITION BY C6 Order by C1) ID,* FROM #temp )T WHERE ID = 1