SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select distinct from one column, return all colmns		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

howie911
Starting Member

2 Posts
Posted - 08/02/2012 :  15:39:13  Show Profile  Reply with Quote
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
Yak Posting Veteran

Canada
97 Posts
Posted - 08/02/2012 :  15:45:36  Show Profile  Reply with Quote
SELECT col1, MAX(COL2), COL3, COL4
FROM TABLE
GROUP BY col1, COL3, COL4
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 08/02/2012 :  15:46:28  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/02/2012 :  16:05:52  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

howie911
Starting Member

2 Posts
Posted - 08/02/2012 :  17:54:36  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 08/02/2012 :  18:24:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/02/2012 :  21:31:54  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000