SQL Ordering and Selecting One Nearest Value

Question

I have a multiple tables and connecting each tables. Result like that:

     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, path have 2 H4, I want to show just 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 that ?

Answer 1

You can make use of 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 minute and GROUP BY

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