Is there a way to return all columns when using distinct?-CodePudding

pleeease help!

I need the highest kilometers value and all other related columns from each distinct VehicleId where SalesAgreementID is 10062 from the table below.

The result should look something like this:

SalesAgreementDetailId | SalesAgreementId | VehicleType | VehicleId | Kilometers | Price
166                    | 10062            | 123         | 000051    | 17377      | 28500.00
169                    | 10062            | 123         | 990140    | 24924      | 28500.00

Tried using:

SELECT DISTINCT VehicleId FROM tblSaleAgreementDetail WHERE SaleAgreementID = '10062';

But it didn't return all the other columns I need including the highest kms.

VehicleId |
000051    |
990140    |

Thaaaankuuu!

CodePudding user response：

In this case you could do this:

select * from tbl where  id = 'xxx' group by field

or this.

select distinct on field * from table

CodePudding user response：

What we usually do, is to rank rows by desired column (kilometers) in descending order and optionally restrict (partition) them by something (vehicle, in this case). That's what analytic functions are for.

What's left is to select values that rank the "highest".

Something like this:

SQL>   with hikm as
  2    (select t.*,
  3       rank() over (partition by vehicleid order by kilometers desc) rnk
  4     from test t
  5    )
  6  select *
  7  from hikm
  8  where rnk = 1;

SALEAGREEMENTDETAILID SALEAGREEMENTID VEHICLETYPE VEHICL KILOMETERS        RNK
--------------------- --------------- ----------- ------ ---------- ----------
                  166           10062         123 000051      17377          1
                  169           10062         123 990140      24924          1

SQL>