12

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

How can I do this?

if column 6 appears like this:

| 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 other columns.

NinjaCat
  • 9,974
  • 9
  • 44
  • 64

7 Answers7

12

In SQL Server 2005 and above:

;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, provided that 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()
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • That gives me: Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'WITH'. Server: Msg 195, Level 15, State 1, Line 4 'ROW_NUMBER' is not a recognized function name. – NinjaCat Aug 19 '10 at 08:04
  • @NinjaCat: which version of `SQL Server` are you using? – Quassnoi Aug 19 '10 at 08:05
  • @NinjaCat: could you please prepend `WITH` with a semicolon and try again? If that fails, could you please post your query exactly as you are running it? – Quassnoi Aug 19 '10 at 08:14
  • I was going to post very similar answers. - my fail. – SQLMason Apr 26 '11 at 15:36
2

The key word "DISTINCT" in SQL has the meaning of "unique value". When applied to a column in a query it will return as many rows from the result set as there are unique, different values for that column. As a consequence it creates a grouped result set, and values of other columns are random unless defined by other functions (such as max, min, average, etc.)

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

If you meant to say you want to return all rows for which Col006 is different from all other values of Col006, then you still need to specify what that value is => see above.

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

UPDATE:

After having seen your edit, I have to point out that if you use any of the other suggestions, you will end up with random values in all other 9 columns for the row that contains the value "item1" in Col006, due to the constraint further up in my post.

wolfgangsz
  • 166
  • 4
  • 3
    I don't care which unique value from col006... I just need one instance. But there are 5000+ different values for col006. How do I specify that? – NinjaCat Aug 19 '10 at 07:56
1

You can group on Col006 to get the distinct values, but then you have to decide what to do with the multiple records in each group.

You can use aggregates to pick a value from the 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 have to identify it somehow. Example of using Col002 to identify the record 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
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
1
select * from yourTable where column6 in (select distinct column6 from yourTable);
Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
sus
  • 27
  • 1
  • 1
    This would return all the values in the table. Because `where column6 in` picks all the rows which has the values in `column6` - that essentially is the whole table. – spacewanderer Nov 18 '21 at 17:47
1
SELECT * 
FROM harvest 
GROUP BY estimated_total;

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

Lets say that you have id as the primary_key. And we want to get all the DISTINCT values for a column lets say estimated_total, And you also need one sample of complete row with each distinct value in SQL. Following query should do the trick.

SELECT *, min(id)
FROM harvest 
GROUP BY estimated_total;
Mohd Abdul Mujib
  • 13,071
  • 8
  • 64
  • 88
1
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 posted above with no luck... but this does the trick!

davesbrain
  • 606
  • 7
  • 12
-1
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
sealz
  • 5,348
  • 5
  • 40
  • 70
banupriya
  • 1,220
  • 5
  • 21
  • 51