Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find Min of the Two columns when multiplied and Grouped Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 28, 2010 5:23 AM
 SSC Rookie Group: General Forum Members Last Login: Sunday, December 19, 2010 10:52 PM Points: 30, Visits: 213
 Hi,I have a table which contain the data as follows,ID first * sec GroupID vid1 1 2 16 1223 2 1 16 1222 3 1 8 334 1 5 16 125 1 6 16 1Here I want to get the minimum of the multiples of the column's(first&sec) that should be grouped with GroupID.For example the result will beID first sec GroupID vid 1 2 2 16 1222 3 1 8 33Can any one help me out of this?It is very urgent...RegardsVarun R
Post #959950
 Posted Wednesday, July 28, 2010 5:49 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 24, 2013 9:02 AM Points: 6, Visits: 55
 Hi Varun,please go through this code. i hope this helps you to get the answers..SELECT a3.*FROM (SELECT a.GroupID, MIN(MULTICOLUMN)MULTICOLUMN FROM (SELECT ID, [FIRST] * SEC MULTICOLUMN, GROUPID FROM SAMPLE_TEST)a JOIN SAMPLE_TEST b On a.ID = b.ID GROUP BY a.GroupID ) a1JOIN (SELECT ID, [FIRST] * SEC MULTICOLUMN, GROUPID FROM SAMPLE_TEST) a2 On a1.GroupID = a2.GroupID AND a1.MULTICOLUMN = a2.MULTICOLUMNJOIN SAMPLE_TEST a3 On a2.ID = a3.IDIni your sample, for the ID value 1 & 3 belongs to same groupID "16" and the multiple for Column first&sec is same. so you would be getting 2 rows for the same groupID 16.If the query has to be changed bit, let me know i can help you in getting the result.All the best,Vijay Vasudevan
Post #959958
 Posted Wednesday, July 28, 2010 5:56 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, December 02, 2010 8:25 AM Points: 1, Visits: 15
 select id,first * sec as dd, sec, groupid, vid from MinMultiplecolumn order by dd
Post #959963
 Posted Wednesday, July 28, 2010 6:09 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 7:09 PM Points: 11,052, Visits: 10,818
 There are many different ways to write a query to do this, here's a popular one:`DECLARE @SampleDataTABLE ( id INTEGER NOT NULL, first INTEGER NOT NULL, sec INTEGER NOT NULL, group_id INTEGER NOT NULL, vid INTEGER NOT NULL, PRIMARY KEY CLUSTERED (id) );INSERT @SampleDataVALUES (1, 1, 2, 16, 122), (3, 2, 1, 16, 122), (2, 3, 1, 08, 033), (4, 1, 5, 16, 012), (5, 1, 6, 16, 001);``SELECT *FROM ( SELECT *, row_num = ROW_NUMBER() OVER ( PARTITION BY S.group_id ORDER BY S.first * S.sec ASC) FROM @SampleData S ) SWHERE S.row_num = 1;` Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #959969
 Posted Wednesday, July 28, 2010 6:12 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 24, 2013 9:02 AM Points: 6, Visits: 55
 your idea is good paul.. Thanks dude.. I got a chance to learn new method today!! :)
Post #959970
 Posted Wednesday, July 28, 2010 6:20 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 7:09 PM Points: 11,052, Visits: 10,818
 But my favourite would probably be this:`DECLARE @SampleDataTABLE ( id INTEGER NOT NULL, first INTEGER NOT NULL, sec INTEGER NOT NULL, group_id INTEGER NOT NULL, vid INTEGER NOT NULL, computed AS ISNULL(first * sec, 0) PRIMARY KEY CLUSTERED (id) );INSERT @SampleDataVALUES (1, 1, 2, 16, 122), (3, 2, 1, 16, 122), (2, 3, 1, 08, 033), (4, 1, 5, 16, 012), (5, 1, 6, 16, 001);``SELECT S.id, S.group_id, S.computedFROM @SampleData SWHERE S.computed = ( SELECT MIN(S2.computed) FROM @SampleData S2 WHERE S2.group_id = S.group_id );`The computed column in the sample table is required to make this work best, but it does not need to be persisted or indexed, so it only really exists in the metadata.This is a workaround for a limitation in the query optimiser, which might not be necessary in future versions.One benefit of the computed column (aside from needing no storage space) is that you can create statistics on it. Or at least you could on a real table - the table variable I used for the demo does not support statistics. Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #959979
 Posted Wednesday, July 28, 2010 6:30 AM
 SSC Rookie Group: General Forum Members Last Login: Sunday, December 19, 2010 10:52 PM Points: 30, Visits: 213
 Dear paulwhite,Thanks for this wonderful solution...Keep it up..Thanks my dear friends for showing interest in answering my question...(For all)Regardsvarun R
Post #959983
 Posted Wednesday, July 28, 2010 6:56 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 7:09 PM Points: 11,052, Visits: 10,818
 You're welcome. Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #960000

 Permissions