Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Ordering and Selecting One Nearest Value

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 ?

like image 296
Harold W Avatar asked Mar 16 '23 17:03

Harold W


2 Answers

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
like image 94
Adriaan Stander Avatar answered Mar 19 '23 07:03

Adriaan Stander


MIN minute and GROUP BY

SELECT CarId, CarLat, CarLon, Path, MIN(Minute), Distance
FROM table
GROUP BY CarId, CarLat, CarLon, Path, Distance
like image 30
Matt Avatar answered Mar 19 '23 07:03

Matt