2

I have below data and I need to calculate LCM (Lowest calculate multiply) value based on group id using a T-SQL query. Your help would be appreciated.

Groupid GroupValue
------------------
1        2
1        4
1        6
2        5
2        5
2       10
3        3
3       12
3        6
3        9

Expected result is below.

Groupid   GroupLCM
------------------
   1        12
   2        10
   3        36
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NP007
  • 659
  • 8
  • 21
  • SQL isn't a good language for mathematically breaking a number down into prime factors. – Robert Sievers Sep 11 '18 at 20:16
  • Doable in pure T-SQL, but neither obvious nor efficient. If you needed this as part of a "real" database (not a toy exercise) I'd probably make it a CLR custom aggregate; `lcm(a,b) = a / gcd(a,b) * b`, and `gcd` is the oldest algorithm in the book. (Literally.) – Jeroen Mostert Sep 11 '18 at 20:19

2 Answers2

2

One possible way is to use tally tables like below

See working demo

; with detailedSet as 
(
    select 
        Groupid,
        GroupValue=abs(GroupValue),
        biggest=max(GroupValue) over (partition by Groupid),
        totalNumbers= count(1) over (partition by Groupid)
    from num
    )
,   
possibleLCMValues as 
(
    select Groupid, counter
    from detailedSet b
    cross apply 
    (
select counter= row_number() over ( order by (select null))  * biggest 
from sys.objects o1 cross join sys.objects o2
)c
where c.counter%GroupValue =0
group by Groupid, counter
having count(1)=max(totalNumbers)
)
,
LCMValues as
(
    select 
        Groupid,
        LCM=min(counter) 
    from possibleLCMValues
    group by Groupid
)

select * from LCMValues
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • I suggest two slight tweaks: 1) to prevent nonsensical results if a group contains only negative numbers, use `abs(GroupValue)` and `max(abs(GroupValue))` in `detailedSet` (`lcm(a,b) = lcm(|a|,|b|)`). 2) change `c.counter < biggest * biggest` to `c.counter / biggest < biggest` to stave off integer overflow. – Jeroen Mostert Sep 11 '18 at 21:30
  • Thank you for your help. But when I have insert one more group id 4 with value 8,9,23 and validate the result then I am not getting group id 4 value in final result set. Might be some condition was not satisfied. – NP007 Sep 12 '18 at 05:13
  • 1
    @Nikunj Yes. The product of other numbers was greater than largest number's square. I decided to remove the stop criteria. – DhruvJoshi Sep 12 '18 at 08:45
  • I am facing some strange issue like when I run same query from http://rextester.com in my ssms then query goes into infinite loop. But it run properly in that link. Any one can check you are also facing the same issue. – NP007 Sep 12 '18 at 17:31
  • Dhruv, Just I inform you like query makes cross join between sys.objects. If your database having more than 1K objects then query goes running and running.. So remove the condition is not the optimize solution. – NP007 Sep 12 '18 at 17:45
  • @Nikunj The cross join is called Tally table. It's a very quick form of in memory table. of course it's a hack based on sys.objects to create a counter in memory quickly. You should add a stopping criteria to cross apply in form of say a where clause which ends the tally to a finite list. But you need to ask yourself a question, at the maximum condition how many co-prime numbers can be in a group? also what's the largest LCM value you are targeting? – DhruvJoshi Sep 12 '18 at 18:32
  • @Nikunj to use another form of tally check here [http://www.sqlservercentral.com/articles/Tally+Table/72993/] – DhruvJoshi Sep 12 '18 at 18:35
  • @Dhruv Can you please explain this Tally table with some example. so far my concept it's not clear. Also I am not fixed largest LCM value because this value purely based on user input. – NP007 Sep 13 '18 at 05:58
0

I found the solution which I post on below stack flow question. In Final result table we just use max value again group id and we get LCM value.

Just note Like, I post this question for more optimize solution to remove for loop otherwise it working properly using for loop as well.

How to update the column without loop in SQL Server?

NP007
  • 659
  • 8
  • 21