Select highest rated, oldest track
- by Blair McMillan
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!
