Select distinct from one column, return all colmns

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

General SQL Server Forums

New to SQL Server Programming

Select distinct from one column, return all colmns

Author

Topic

howie911
Starting Member

2 Posts

Posted - 2012-08-02 : 15:39:13

MSSQL 2008 R2

From one table I want all the rows with a distinct value in one column (col3), but I want to see all the columns returned. For the sake of illustration, I'm using only four columns here. There are actually 28 columns in my table.

Source:
col1-|-col2-|-col3-|-col4
-----|------|------|-----
123--|-PETE-|-NY---|-DEAD
124--|-SAM--|-NJ---|-ALIVE
125--|-JOE--|-TX---|-DEAD
126--|-SUE--|-NY---|-ALIVE
127--|-SUE--|-NJ---|-ALIVE

Desired output:
col1-|-col2-|-col3-|-col4
-----|------|------|-----
123--|-PETE-|-NY---|-DEAD
124--|-SAM--|-NJ---|-ALIVE
125--|-JOE--|-TX---|-DEAD

When I use this query, all rows are returned, not just the rows for a unique value in col3:

SELECT *
FROM table
WHERE col3 IN (SELECT DISTINCT col3 FROM table)

From my searches on the interwebs, many others have this same problem, with many solutions offered, none of which work.

Thanks in advance.

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-08-02 : 15:45:36

SELECT col1, MAX(COL2), COL3, COL4
FROM TABLE
GROUP BY col1, COL3, COL4

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 15:46:28

You can do the following - which of the many rows for a given col3 value will be returned is determined by the ordering you specify in the ROW_NUMBER().

SELECT
	col1, col2, col3,col4
FROM
(
	SELECT
		*,ROW_NUMBER() OVER (PARTITION BY col3 ORDER BY col1) AS RN
	FROM
		YourTable
)s
WHERE RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 16:05:52

[code]
SELECT t.*
FROM table t
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM table
WHERE col3= t.col3
AND col1 < t.col1) t1
WHERE COALESCE(Cnt,0)=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

howie911
Starting Member

2 Posts

Posted - 2012-08-02 : 17:54:36

Arggg!

Apologies. Although I am using SQL Server 2008 R2 engine, the database itself is 2000. The ROW_NUMBER and OUTER APPLY functions are not available to me.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-02 : 18:24:01

Maybe this:

SELECT A.*
FROM Table AS A
INNER JOIN
(
	SELECT MIN(col1)
	FROM TABLE
	GROUP BY Col2
) AS B
ON A.col1 = B.Col1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 21:31:54

quote:

Originally posted by Lamprey

Maybe this:

SELECT A.*
FROM Table AS A
INNER JOIN
(
	SELECT MIN(col1) AS Col1
	FROM TABLE
	GROUP BY Col2
) AS B
ON A.col1 = B.Col1

I hope Col1 is unique valued field as shown in sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources