SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Min of the Two columns when multiplied and Grouped


Find Min of the Two columns when multiplied and Grouped

Author
Message
varunfilim
varunfilim
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 213
Hi,

I have a table which contain the data as follows,

ID first * sec GroupID vid
1 1 2 16 122
3 2 1 16 122
2 3 1 8 33
4 1 5 16 12
5 1 6 16 1

Here 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 be


ID first sec GroupID vid

1 2 2 16 122

2 3 1 8 33


Can any one help me out of this?It is very urgent...


Regards

Varun R
Vijay Vasudevan
Vijay Vasudevan
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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
) a1
JOIN (SELECT ID, [FIRST] * SEC MULTICOLUMN, GROUPID FROM SAMPLE_TEST) a2 On a1.GroupID = a2.GroupID AND a1.MULTICOLUMN = a2.MULTICOLUMN
JOIN SAMPLE_TEST a3 On a2.ID = a3.ID

Ini 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
muralikannan-424460
muralikannan-424460
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
select id,first * sec as dd, sec, groupid, vid from MinMultiplecolumn order by dd
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36088 Visits: 11361
There are many different ways to write a query to do this, here's a popular one:


DECLARE @SampleData
TABLE (
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 @SampleData
VALUES (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
) S
WHERE S.row_num = 1;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Vijay Vasudevan
Vijay Vasudevan
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 55
your idea is good paul.. Thanks dude..

I got a chance to learn new method today!! Smile
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36088 Visits: 11361
But my favourite would probably be this:

DECLARE @SampleData
TABLE (
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 @SampleData
VALUES (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.computed
FROM @SampleData S
WHERE 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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
varunfilim
varunfilim
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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)







Regards
varun R
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36088 Visits: 11361
You're welcome.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search