Order SQL and select one closest value

Question

I have several tables and a join for each table. Result:

     CarId   CarLat   CarLon   Path   Minute   Distance
      325    36.000   37.200    H4      74       250
      344    36.050   37.040    H6      75       500
      365    36.300   37.600    H4      76       750
      311    36.060   37.080    H5      77       800

As you can see, the path has 2 H4, I want to show only a smaller minute path. Like this:

     CarId   CarLat   CarLon   Path   Minute   Distance
      325    36.000   37.200    H4      74       250
      344    36.050   37.040    H6      75       500
      311    36.060   37.080    H5      77       800

How can i do this?

Answer 1

You can use SQL SERVER ROW_NUMBER to determine this.

So something like

;WITH Vals AS (
        SELECT  *,
                ROW_NUMBER() (PARTITION BY Path ORDER BY Minute) RowID
        FROM    Table
)
SELECT  *
FROM    Vals
WHERE   RowID = 1

Answer 2

MIN MINUT and GROUP BY

SELECT CarId, CarLat, CarLon, Path, MIN(Minute), Distance
FROM table
GROUP BY CarId, CarLat, CarLon, Path, Distance