Removing count column from query output-CodePudding

select top (5) t3.Model, t3.Manufacturer, t1.Colour, t1.RegistrationNumber, t1.DailyRentalPrice, count(t2.TruckID) as RentedAmount

from [IndividualTruck-PB] t1 

inner join [TruckRental-PB] t2 on t1.TruckID = t2.TruckID
inner join [TruckModel-PB] t3 on t1.TruckModelID = t3.ModelID

group by t3.Model, t3.Manufacturer, t1.Colour, t1.RegistrationNumber, t1.DailyRentalPrice

order by RentedAmount desc

Bsically, I'm trying to get the top 5 most rented but don't want the actual count column as output only as a means of ordering the output. Is this possible?

CodePudding user response：

You can try remove the count column and give the formula to order by part:

select top (5) t3.Model, t3.Manufacturer, t1.Colour, t1.RegistrationNumber, t1.DailyRentalPrice

from [IndividualTruck-PB] t1 

inner join [TruckRental-PB] t2 on t1.TruckID = t2.TruckID
inner join [TruckModel-PB] t3 on t1.TruckModelID = t3.ModelID

group by t3.Model, t3.Manufacturer, t1.Colour, t1.RegistrationNumber, t1.DailyRentalPrice

order by count(t2.TruckID) desc

My test:

create table A (
  col1 varchar(255)
);

insert into A (col1) values ('A');
insert into A (col1) values ('A');
insert into A(col1) values ('A');
insert into A(col1) values ('A');
insert into A(col1) values ('A');
insert into A(col1) values ('A');
insert into A(col1) values ('A');
insert into A(col1) values ('B');
insert into A(col1) values ('B');
insert into A(col1) values ('B');
insert into A(col1) values ('B');
insert into A(col1) values ('B');
insert into A(col1) values ('B');
insert into A(col1) values ('C');
insert into A(col1) values ('C');
insert into A(col1) values ('C');
insert into A(col1) values ('C');
insert into A(col1) values ('C');
insert into A(col1) values ('D');
insert into A(col1) values ('D');
insert into A(col1) values ('D');
insert into A(col1) values ('D');
insert into A(col1) values ('D');
insert into A(col1) values ('E');
insert into A(col1) values ('E');
insert into A(col1) values ('E');

Select doesn't work in mysql 5.6, but does in MS SQL Server 2017:

select top(2) col1 from A group by col1 order by count(col1) desc;

Output:

col1
A
B