quote:

---

Originally posted by werhardt

They are all assigned with an id in our data.

Here is a little sample :)

I want to just pull the 1 pcs_id1 record. I don't want to see multiple addresses. I just want to see one of them.

pcs_id1 L_name F_name Address
00093685 ABARBANEL JACK 815 FREEPORT ROAD
00093685 ABARBANEL JACK 1604 BURTNER ROAD
00093685 ABARBANEL JACK 565 COAL VALLEY ROAD

---

SELECT pcs_id1,L_name,F_name,MAX(Address) AS Address
FROM YourTable
GROUP BY pcs_id1,L_name,F_name

quote:

---

Originally posted by X002548

No,

Max takes the MAX from a set of data...it doesn't mean that they will all be on the same row...

Do you have an add or update datetime column on the row?

Post the DDL of the table



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

CREATE TABLE myTable99(pcs_id1 varchar(25), L_name varchar(25), F_name varchar(25)
, Address varchar(25), City varchar(10))
GO

INSERT INTO myTable99(pcs_id1, L_name, F_name, Address, City)

SELECT '00093685', 'ABARBANEL', 'JACK', '815 FREEPORT ROAD', 'Newark' UNION ALL
SELECT '00093685', 'ABARBANEL', 'JACK', '1604 BURTNER ROAD', 'New York' UNION ALL
SELECT '00093685', 'ABARBANEL', 'JACK', '565 COAL VALLEY ROAD', 'New Ark' UNION ALL
SELECT '00093686', 'KMan', 'Brett', '815 FREEPORT ROAD','Boston' UNION ALL
SELECT '00093686', 'KMan', 'Brett', '1604 BURTNER ROAD', 'London' UNION ALL
SELECT '00093686', 'KMan', 'Brett', '565 COAL VALLEY ROAD', 'Paris'
GO

SELECT * FROM myTable99
GO

CREATE TABLE #myTemp99 (  ColOrder int IDENTITY(1,1)
			, pcs_id1 varchar(25), L_name varchar(25), F_name varchar(25)
, Address varchar(25), City varchar(10))
GO

INSERT INTO #myTemp99 (pcs_id1, L_name, F_name, Address, City)
     SELECT pcs_id1, L_name, F_name, Address, City
       FROM myTable99
   ORDER BY pcs_id1, City
GO

SELECT * FROM #myTemp99 ORDER BY ColOrder
GO

SELECT * 
  FROM #myTemp99 o
 WHERE EXISTS (SELECT pcs_id1 
		 FROM #myTemp99 i 
		WHERE i.pcs_id1 = o.pcs_id1  
	     GROUP BY pcs_id1
               HAVING MAX(i.ColOrder) = o.ColOrder) 
GO

DROP TABLE myTable99, #myTemp99
GO