Hi,I'm moving across to SQL Server after using MySQL and having trouble with one particular query. My tables are outlined below. I'm trying to get hold of the names of bands playing the most recently added shows but can't quite get there.
Shows table = Id.. some other columns.. DateAdded
Bands table = Id, Name
Playing table = Id, Show_Id, Band_Id
My query at the moment is
SELECT Bands.Name FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1
ORDER BY Shows.DateAdded DESC
Which gives me the right results but will return duplicate band names if there is more than one new show for that band. I've tried doing SELECT DISTINCT Bands.Name but get an error saying "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". I also tried using a GROUP BY clause but I can't seem to combine it with the order by clause without getting an error.
I've tried creating a view which handles the joining and selecting distinct Names from the view but this is ignoring the ordering and just returns band names in alphabetical order.
I hope you understand my problem and I really HOPE someone has a solution even if it means returning some extra columns which I can just ignore in my page logic. Thanks.
SELECT Bands.Name, MAX(Shows.DateAdded) FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1
GROUP BY Bands.Name
ORDER BY Shows.DateAdded DESC
Thanks for the reply but that doesn't work either. The error I get the same I error I was getting when I tried using GROUP BY...
Msg 8127, Level 16, State 1, Line 1
Column "Shows.DateAdded" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
You could try a slight variation of that:
SELECT Bands.Name, MAX(Shows.DateAdded) FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1
GROUP BY Bands.Name
ORDER BY 2 DESC
or
SELECT Bands.Name, MAX(Shows.DateAdded) FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1
GROUP BY Bands.Name
ORDER BY MAX(Shows.DateAdded) DESC
