retrive 1 from a grouping - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

retrive 1 from a grouping

Author

Topic

dporter
Starting Member

9 Posts

Posted - 2005-09-13 : 13:26:02

I need to be able to retrieve one record for each grouping. For example, in the data I have supplied, the item 2000185 has three records and 2000170 has two. If there is a difference in the date, I need to return the most recent date. I need to retrieve 1 record for these, so the result set would look like:
2000185 widget1 100 8/25/2005
2000189 widget2 150 3/25/2005
2000170 widget1 50 2/24/2004

CREATE TABLE blah
(item varchar(10),
Product_Name varchar(100),
Age int,
last_ship_date smalldatetime)

INSERT INTO blah VALUES ('2000185', 'widget1', 100, '8/25/2005')
INSERT INTO blah VALUES ('2000185', 'widget1', 101, '8/25/2005')
INSERT INTO blah VALUES ('2000185', 'widget1', 100, '6/25/2005')
INSERT INTO blah VALUES ('2000189', 'widget2', 150, '3/25/2005')
INSERT INTO blah VALUES ('2000170', 'widget1', 50, '2/24/2004')
INSERT INTO blah VALUES ('2000170', 'widget1', 50, '2/24/2004')

DROP TABLE blah

I appreciate any help you can provide.
Dennis

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-13 : 13:41:55

What do you want to do with ties?


SELECT * 
  FROM blah o 
 WHERE EXISTS ( SELECT * 
		  FROM blah i 
		 WHERE o.item = i.item 
	      GROUP BY item 
		HAVING o.last_ship_date = MAX(i.last_ship_date))

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

dporter
Starting Member

9 Posts

Posted - 2005-09-13 : 13:52:08

Thanks for your prompt response Brett.
If there is a tie between the last_ship_date, then I need to get one value, the idea being that I need to show the last ship date for the item #. Therefore, if it is a tie, any one of them will do.

quote:
Originally posted by X002548

What do you want to do with ties?

SELECT *
FROM blah o
WHERE EXISTS ( SELECT *
FROM blah i
WHERE o.item = i.item
GROUP BY item
HAVING o.last_ship_date = MAX(i.last_ship_date))

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 01:13:48

Try this also

Select item, Product_Name, min(Age),max(last_ship_date) from blah group by item, Product_Name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources