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

Question

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!

Does this answer your question? [MySQL select one column DISTINCT, with corresponding other columns](https://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns) — steven7mwesigwa, Jan 14 '22 at 19:42

score 0 · Answer 1 · answered Jan 14 '22 at 19:35

0

In this case you could do this:

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

or this.

select distinct on field * from table

answered Jan 14 '22 at 19:35

noe

11
3

Where field is? I get a syntax error on both: Column 'tblSaleAgreementDetail.SaleAgreementDetailId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Incorrect syntax near the keyword 'ON'. – Sam Riveros Jan 14 '22 at 20:43
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 14 '22 at 21:48

score 0 · Answer 2 · answered Jan 14 '22 at 20:10

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>

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

2 Answers2