this sql query works in mysql but not in mssql

Question

mysql:

SELECT * 
FROM (select * from songs order by artist asc) as songs2 
WHERE artist LIKE 'a%' GROUP BY artist`

mssql:

SELECT * 
FROM (select top 1000 * from songs order by artist asc) as songs2  
WHERE artist LIKE 'a%' GROUP BY artist

This works in mysql but in mssql i get an error:

Column 'songs2.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

++++++++++++++++++ now i select with this query : SELECT artist,album,song from (select top 1000 * from songs order by year asc, artist asc) as songs2 where artist like 'a%' group by artist,album,song; but it does not group by artist, i want only one artist per song available in the query

Do what the error message says and remove the clause. It doesn't really do anything (there's no guarantee that the outer query respects that order, even though it'll probably do it). — Álvaro González
– Álvaro González, Commented Jul 30, 2015 at 8:28

Jens · Accepted Answer · 2015-07-30 08:28:07Z

1

In mysql you can select values which are not in group by statement. It is not possible in mssql.

SELECT artist FROM 
(
    select * from songs 
) as songs2 
where artist like 'a%' group by artist;

And also you can not use order by in a subquery.

answered Jul 30, 2015 at 8:28

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

t-clausen.dk Over a year ago

actually you can use ORDER BY in a subquery when combined with TOP on sqlserver

t-clausen.dk · Accepted Answer · 2015-07-30 08:57:42Z

0

This will find 1 song per artist and a maximum of 1000 songs:

SELECT TOP 1000 
  artist,album,song 
FROM 
(
  SELECT 
    artist,album,song, 
    row_number() over (partition by artist order by (SELECT 1)) rn
  FROM table_name 
  WHERE artist like 'a%' 
) as t2 
WHERE rn = 1

answered Jul 30, 2015 at 8:57

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Comments

gotqn · Accepted Answer · 2015-07-30 09:46:25Z

0

Remove the ORDER BY clause:

SELECT * FROM 
(
    select * from songs 
) as songs2 
where artist like 'a%';

There is no point to order the rows in the sub-query as you are not using TOP to get N of them, OFFSET to apply paging or FOR XML to order XML nodes.

If you want to order the results, do it in the outer query:

SELECT * FROM 
(
    select * from songs 
) as songs2 
where artist like 'a%'
order by year asc, artist asc;

So, basically, you can shorted this:

SELECT *  
from songs 
where artist like 'a%' 
order by year asc, artist asc;

edited Jul 30, 2015 at 9:46

answered Jul 30, 2015 at 8:29

gotqn

44k47 gold badges167 silver badges257 bronze badges

2 Comments

shervin khaleghjoo Over a year ago

]Column 'songs2.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

gotqn Over a year ago

@shervinkhaleghjoo Remove the GROUP BY clause or add all columns in it.

Dhaval · Accepted Answer · 2015-07-30 09:46:44Z

0

Order By in subquery Require TOP/OFFSET/FOR XML Cluase

SELECT artist FROM 
(
    select top 100 percent * from songs 
    order by year asc, artist asc
) as songs2 
where artist like 'a%' group by artist;

edited Jul 30, 2015 at 9:46

answered Jul 30, 2015 at 8:30

Dhaval

2,3791 gold badge15 silver badges16 bronze badges

2 Comments

shervin khaleghjoo Over a year ago

]Column 'songs2.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

shervin khaleghjoo Over a year ago

I want to know the song name also, only artist is not enough

Collectives™ on Stack Overflow

this sql query works in mysql but not in mssql

4 Answers 4

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related