CODEWITHSUNDEEP
sqlsql-server
Anjali
Anjali

Reputation: 163

How can I replace the MySQL code in MS-SQL

I have written following code in Mysql. But now I want to run this same code in MS-SQL server. How do I do that?! I dont know some syntax in MS-SQL.

Code:

select 
    t2.gname 
from
(select 
    sum(t1.strength) as sst, 
    group_concat(t1.name order by t1.name) as gname 
    from 
    (select 
         a.name, 
         a.strength, 
         group_concat(d.zone order by d.zone) as zones 
         from Animals a 
         inner join Details d on a.id = d.id 
         group by a.id) t1 
    group by t1.zones) t2 
    order by t2.sst desc limit 1;

I got the correct output in MySQL. But how I can change it for MS-SQL?

Please help! Note question pertaining to this solution is : Problem statement

Upvotes: 0

Views: 105

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest:

select top (1) string_agg(a.name, ',') within group (order by a.name) as gname 
from (select a.name, a.strength, 
             string_agg(d.zone, ',') within group (order by d.zone) as zones 
      from Animals a join
           Details d
           on a.id = d.id 
      group by a.id, a.name, a.strength
     ) a
group by a.zones
order by sum(a.strength) desc;

In other words, the outer query is not needed -- either in MySQL or SQL Server.

Upvotes: 1

DaveWuzHere
DaveWuzHere

Reputation: 255

So pretty much limit > top 1 and group_concat > string_agg

I would've liked a way of testing this, but I think this is what you are after:

select top 1 t2.gname from
(select sum(t1.strength) as sst, 
string_agg(t1.name, '')
within group (order by t1.name) as gname 
from 
(select a.name, a.strength, 
string_agg(d.zone, ''
within group (order by d.zone) as zones 
from Animals a 
inner join Details d on a.id = d.id 
group by a.id) t1 group by t1.zones) t2 
order by t2.sst desc

Upvotes: 0

Related Questions