Select highest rated, oldest track

Question

I have several tables:

CREATE TABLE [dbo].[Tracks](
    [Id] [uniqueidentifier] NOT NULL,
    [Artist_Id] [uniqueidentifier] NOT NULL,
    [Album_Id] [uniqueidentifier] NOT NULL,
    [Title] [nvarchar](255) NOT NULL,
    [Length] [int] NOT NULL,
 CONSTRAINT [PK_Tracks_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TrackHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Track_Id] [uniqueidentifier] NOT NULL,
    [Datetime] [datetime] NOT NULL,
 CONSTRAINT [PK_TrackHistory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO [cooltunes].[dbo].[TrackHistory]
           ([Track_Id]
           ,[Datetime])
     VALUES
           ("335294B0-735E-4E2C-8389-8326B17CE813"
           ,GETDATE())



CREATE TABLE [dbo].[Ratings](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Track_Id] [uniqueidentifier] NOT NULL,
    [User_Id] [uniqueidentifier] NOT NULL,
    [Rating] [tinyint] NOT NULL,
 CONSTRAINT [PK_Ratings] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [cooltunes].[dbo].[Ratings]
           ([Track_Id]
           ,[User_Id]
           ,[Rating])
     VALUES
           ("335294B0-735E-4E2C-8389-8326B17CE813"
           ,"C7D62450-8BE6-40F6-80F1-A539DA301772"
           ,1)


Users
User_Id|Guid
Other fields

Links between the tables are pretty obvious.

TrackHistory has each track added to it as a row whenever it is played ie. a track will appear in there many times.

Ratings value will either be 1 or -1.

What I'm trying to do is select the Track with the highest rating, that is more than 2 hours old, and if there is a duplicate rating for a track (ie a track receives 6 +1 ratings and 1 - rating, giving that track a total rating of 5, another track also has a total rating of 5), the track that was last played the longest ago should be returned. (If all tracks have been played within the last 2 hours, no rows should be returned)

I'm getting somewhere doing each part individually using the link above, SUM(Value) and GROUP BY Track_Id, but I'm having trouble putting it all together.

Hopefully someone with a bit more (MS)SQL knowledge will be able to help me. Many thanks!

Answer 1

select top 1 t.Id, SUM(r.Rating) as Rating, MAX(Datetime) as LastPlayed
from Tracks t
inner join TrackHistory h on t.Id = h.Track_Id
inner join Ratings r on t.Id = r.Track_Id
where h.Track_Id not in (
    select Track_Id 
    from TrackHistory 
    where Datetime > DATEADD(HOUR, -2, getdate())
)
group by t.Id
order by Rating desc, LastPlayed