July 22, 2010 at 9:22 am
Hi,
I have a table called 'Application' . It contains 4 columns like 'appid, appname, version, appCount'
appid appname version appCount
--------------------------------------------------------
1Access 200012
2Access 200134
3Access 20027
--------------------------------------------------------
Now I want to take Max-appid and Min-appid based on the appcount
Expected
appid appname version appCount 'MajorId' 'MinorId'
------------------------------------------------------------
1Access 2000 12 2 3
2Access 2001 34 2 3
3Access 2002 7 2 3
--------------------------------------------------------------
Pls help me on this..
Regards,
Ram
July 22, 2010 at 10:14 am
Friend, when I have read your question first time I thought that you are drunk 😀
After looking it closely I think now I understand what you want.
Assuming that the grouping is appname, the following will show the solution for your problem:
-- setup test data
declare @Application table (appid int, appname varchar(30), [version] int, appCount int)
insert @Application
select 1, 'Access', 2000, 12
union select 2, 'Access', 2001, 34
union select 3,'Access', 2002,7
union select 5, 'Excel', 2000, 54
union select 6, 'Excel', 2001, 6
union select 7,'Excel', 2002,25
;with mm
as
(
select appname, max(appCount) mxCnt, min(appCount) mnCnt
from @Application
group by appname
)
,mmId
as
(
select a.appname , sum(case when a.appCount = mm.mxCnt then appId else 0 end) MajorId
, sum(case when a.appCount = mm.mnCnt then appId else 0 end) MinorId
from @Application a
join mm on mm.appname = a.appname
group by a.appname
)
select a.*, mm.MajorId, mm.MinorId
from @Application a
join mmid mm on mm.appname = a.appname
Please click the link in my signature, so next time you will present your question in a way which will attract quick and better answer!
July 22, 2010 at 10:18 am
This is possible a couple of different ways, but a couple of quick questions first...
Is it possible to have more than one appid equal to the max or min appcount? Building on your example: if you had an appid of 4 with an appcount of 7, an appid of 5 with an appcount of 34, etc... how would you want the output displayed?
July 22, 2010 at 10:20 am
Here's one way
declare @t table (AppID int, AppName varchar(10), Version int, AppCount int)
insert into @t
select 1,'Access',2000, 12 union all
select 2,'Access',2001, 34 union all
select 3,'Access',2002, 7
select a.AppID, a.AppName, a.Version, a.Appcount, c.AppID MajorID, d.AppID MinorID
from @t a
inner join (select AppName, min(AppCount) MinorID, max(Appcount) MajorID
from @t
group by AppName) b
on a.AppName = b.AppName
inner join @t c
on c.AppName = b.AppName
and c.AppCount = b.MajorID
inner join @t d
on d.AppName = b.AppName
and d.AppCount = b.MinorID
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/
July 26, 2010 at 5:24 am
Hi all
Thanks for your post. Could you please try with the following data..
I couldnt get the expected result using the above queries.
select 1, 'Excel',2000, 13 union all
select 2, 'Access',2001, 34 union all
select 3, 'Access',2002, 70 union all
select 4, 'Access',2002, 27 union all
select 5, 'ABC',2002, 27 union all
select 6, 'ABC',2005, 27
Regards,
Ram
July 26, 2010 at 5:52 am
Try this:
-- setup test data
declare @Application table (appid int, appname varchar(30), [version] int, appCount int)
insert @Application
select 1, 'Excel',200, 3 union all
select 2, 'Access',2001, 34 union all
select 3, 'Access',2002, 7 union all
select 4, 'Access',2002, 27 union all
select 5, 'ABC',2002, 27 union all
select 6, 'ABC',2005, 27 ;
--select * from @Application
with max_min
as
(
select appname, max(appCount) mxCnt, min(appCount) mnCnt
from @Application
group by appname
),
minmax_ID As
(
select App.AppName , App.Appid from max_min mm
inner join @Application App
on (mm.AppName = App.AppName AND
mm.mxCnt = App.appCount )
OR
(mm.AppName = App.AppName AND
mm.mnCnt = App.appCount )
),
final_minMAx as
(
select appname, max(Appid) mxid, min(Appid) mnid
from minmax_ID
group by appname
)
select * from @Application App
cross join final_minMAx fmm
where App.AppName = fmm.appname
Hope this helps
July 26, 2010 at 6:00 am
DROP TABLE #Application
CREATE TABLE #Application (appid INT, appname VARCHAR(20), version INT, appCount INT)
INSERT INTO #Application (appid, appname, version, appCount)
select 1, 'Excel', 2000, 13 union all
select 2, 'Access', 2001, 34 union all
select 3, 'Access', 2002, 70 union all -- max
select 4, 'Access', 2002, 27 union all -- min
select 5, 'ABC', 2002, 27 union all
select 6, 'ABC', 2005, 27
SELECT a.appid, a.appname, a.version, a.appCount, q.MinAppID, q.MaxAppID
FROM #Application a
INNER JOIN (
SELECT appname,
MinAppID = min(CASE WHEN Seq = 1 THEN AppID END),
MaxAppID = max(CASE WHEN Seq = [Groupsize] THEN AppID END)
FROM (
SELECT appname, AppID,
[Seq] = ROW_NUMBER() OVER (PARTITION BY appname ORDER BY appCount),
[Groupsize] = COUNT(*) OVER(PARTITION BY appname)
FROM #Application
) d WHERE Seq = 1 OR Seq = [Groupsize]
GROUP BY appname
) q ON q.appname = a.appname
ORDER BY a.appid
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 26, 2010 at 6:21 am
I wonder, for
select 5, 'ABC', 2002, 27 union all
select 6, 'ABC', 2005, 27
Do you want to see the same Appid for MajorId and MinorId?
Which one you wnt to see 5 or 6 and why?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply