September 1, 2009 at 12:17 am
Hi,
I need to show an extra column in column (date) in a group by clause, but when i use that in the query it gives more rows than i actually need.
[Code]
declare @tbl table
(
sort int,
type varchar(100),
date datetime,
price decimal(12,2),
name varchar(100)
)
insert into @tbl
select 1,'R','1/1/2009',1,'A'
union
select 1,'R','1/2/2009',0,'A'
union
select 2,'M','1/1/2009',3,'A'
union
select 2,'M','1/2/2009',4,'A'
union
select 3,'N','1/3/2009',6,'A'
expected output
----------------
sorttypedatepricename
------------------------------------------------------
1R1/1/2009 1 A
2M1/2/2009 4 A
3N1/3/2009 6 A
[/Code]
This is the query i m using,
[Code]
select sort,type,Max(price),name from @tbl group by sort,type,name
[/Code]
Thanks.
September 1, 2009 at 12:31 am
Repeated post, discussions already started in
September 1, 2009 at 12:34 am
arun.sas (9/1/2009)
Repeated post, discussions already started in
Sorry, this actually had to be in SQL2005 forum as it used table datatype in sample query, by mistake i posted in SQL2000.
September 2, 2009 at 5:32 am
decentflower,
Try this:
SELECT * FROM @tbl t JOIN
(select sort,type, Max(price) AS price , name from @tbl group by sort,type,name) mmax
ON t.sort = mmax.sort AND t.type = mmax.type AND t.name = mmax.name AND t.price = mmax.price
ORDER BY t.sort
Although, I would suggest better (more descriptive) choices for table aliases.... 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply