SQLize | PHPize | SQLtest

CREATE TABLE [Companies]( [CompanyID] [int] NOT NULL, [Name] [nvarchar](255) NOT NULL, CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED (CompanyID) ) GO CREATE TABLE [dbo].[CompanyRatings]( [RowID] [int] NOT NULL, [CompanyID] [int] NOT NULL, [ReportDate] [date] NOT NULL, [Rating] [tinyint] NOT NULL, CONSTRAINT [PK_CompanyRatings] PRIMARY KEY CLUSTERED (RowID) ) GO INSERT [dbo].[Companies] ([CompanyID], [Name]) VALUES (1, N'Company 1'), (2, N'Company 2'), (3, N'Company 3'), (4, N'Company 4'), (5, N'Company 5'), (6, N'Company 6'), (7, N'Company 7'), (8, N'Company 8'), (9, N'Company 9'), (10, N'Company 10') GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (1, 1, CAST(N'2021-07-18' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (2, 4, CAST(N'2022-02-17' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (3, 8, CAST(N'2020-04-02' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (4, 2, CAST(N'2020-12-25' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (5, 9, CAST(N'2022-08-03' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (6, 4, CAST(N'2020-01-04' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (7, 3, CAST(N'2020-03-11' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (8, 2, CAST(N'2022-09-02' AS Date), 5) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (9, 5, CAST(N'2021-12-11' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (10, 3, CAST(N'2020-02-22' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (11, 6, CAST(N'2022-10-29' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (12, 3, CAST(N'2022-02-20' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (13, 2, CAST(N'2021-03-28' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (14, 2, CAST(N'2021-04-27' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (15, 9, CAST(N'2021-03-30' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (16, 1, CAST(N'2022-10-18' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (17, 5, CAST(N'2020-03-20' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (18, 7, CAST(N'2020-02-14' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (19, 8, CAST(N'2022-01-20' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (20, 2, CAST(N'2020-09-29' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (21, 2, CAST(N'2020-10-02' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (22, 8, CAST(N'2020-09-06' AS Date), 5) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (23, 4, CAST(N'2020-10-29' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (24, 5, CAST(N'2021-07-17' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (25, 9, CAST(N'2021-01-02' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (26, 9, CAST(N'2020-01-05' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (27, 7, CAST(N'2022-02-12' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (28, 9, CAST(N'2022-08-05' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (29, 2, CAST(N'2022-01-20' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (30, 7, CAST(N'2022-11-14' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (31, 9, CAST(N'2022-09-02' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (32, 6, CAST(N'2022-09-19' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (33, 3, CAST(N'2022-11-18' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (34, 5, CAST(N'2020-08-21' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (35, 3, CAST(N'2022-02-18' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (36, 3, CAST(N'2021-12-15' AS Date), 3) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (37, 9, CAST(N'2020-01-17' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (38, 8, CAST(N'2020-05-28' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (39, 2, CAST(N'2022-07-11' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (40, 2, CAST(N'2022-03-15' AS Date), 5) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (41, 2, CAST(N'2022-01-02' AS Date), 4) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (42, 8, CAST(N'2022-03-16' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (43, 5, CAST(N'2023-01-01' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (44, 2, CAST(N'2020-08-06' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (45, 7, CAST(N'2022-03-22' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (46, 3, CAST(N'2022-01-01' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (47, 5, CAST(N'2021-01-15' AS Date), 2) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (48, 3, CAST(N'2020-11-17' AS Date), 5) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (49, 3, CAST(N'2021-10-16' AS Date), 1) GO INSERT [dbo].[CompanyRatings] ([RowID], [CompanyID], [ReportDate], [Rating]) VALUES (50, 3, CAST(N'2020-04-14' AS Date), 2) GO ALTER TABLE [dbo].[CompanyRatings] WITH CHECK ADD CONSTRAINT [FK_CompanyRatings_Companies] FOREIGN KEY([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID]) GO ALTER TABLE [dbo].[CompanyRatings] CHECK CONSTRAINT [FK_CompanyRatings_Companies] GO ;with LessThan4 as ( select CompanyID from CompanyRatings group by CompanyID having max(Rating)<4 ) select c.*, lr.LastRating from Companies c inner join LessThan4 l4 on c.CompanyID=l4.CompanyID cross apply (select top 1 Rating LastRating from CompanyRatings where CompanyID=l4.CompanyID order by ReportDate desc) lr go