August 31, 2009 at 10:05 pm
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.
August 31, 2009 at 11:47 pm
Hi,
try this
select x.*,y.date as [date]
from
(
select sort,type,Max(price)price,name from @tbl
group by sort,type,name
) as X
left outer join
@tbl Y
on x.sort = y.sort
and x.type = y.type
and x.name = y.name
and x.price = y.price
September 1, 2009 at 12:07 am
hi,
but what if all values are same and only date is diiferent for 2 rows.
then which date will be selected ?
like for example
[Code]
insert into @tbl
select 1,'R','1/1/2009',1,'A'
union
select 1,'R','1/2/2009',1,'A'
union
select 2,'M','1/1/2009',3,'A'
union
select 2,'M','1/2/2009',3,'A'
union
select 3,'N','1/3/2009',6,'A'
[/Code]
September 1, 2009 at 5:36 am
If all the data is the same except the dates which row should be chosen?
September 1, 2009 at 6:20 am
Then maximum of the two dates should be chosen.
September 1, 2009 at 7:05 am
Once you find the max price then you can find the max date. Try this
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'
select a.sort, a.type, max(a.Date), b.Price, a.Name
from @tbl a
inner join (select sort, type, max(Price) Price
from @tbl
group by sort, type, Name) B
on a.Sort = b.Sort
and a.Type = b.type
and a.Price = b.Price
group by a.sort, a.type, b.Price, a.Name
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2009 at 12:10 am
Hi, Sorry for late reply . i wasnt on desktop for a while..
continuing ..
cant i use this
[Code]
select sort,type,Max(price),Max(Date),name from @tbl group by sort,type,name
[/Code]
September 22, 2009 at 4:06 am
Hi,
I used this qurey on a table with 2Lakhs and 80 thousands records
[Code]
select a.sort, a.type, max(a.Date), b.Price, a.Name
from @tbl a
inner join (select sort, type, max(Price) Price
from @tbl
group by sort, type, Name) B
on a.Sort = b.Sort
and a.Type = b.type
and a.Price = b.Price
group by a.sort, a.type, b.Price, a.Name
[/Code]
The query dies and takes 1.5 minutes to execute, and sometimes never returns.
Can i optimize this further?
September 22, 2009 at 4:18 am
[Code]
select a.sort, a.type, max(a.Date), b.Price, a.Name
from @tbl a
inner join (select sort, type, max(Price) Price
from @tbl
group by sort, type, Name) B
on a.Sort = b.Sort
and a.Type = b.type
and a.Price = b.Price
group by a.sort, a.type, b.Price, a.Name
[/Code]
Hi,
I thought you confused your requirement, is what type out put you received from above statement?
Use the statement what I posted on 9/1/2009, its suits your requirement.
September 22, 2009 at 4:28 am
Hi,
But the one u said returns more than one row if all columns are same and date is different.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply