CODEWITHSUNDEEP
sqllinqt-sql
freakdev
freakdev

Reputation: 75

SQL/Linq GroupBy with Min Value and depending column

I have a table with these example rows:

CouponID ShopID ShopLongitude ShopLatitude
365      1881   55,5574105    9,9613295
365      23550  55,5510846    9,9936818
365      33550  55,6220936    10,0663895
365      33551  55,5573436    9,9611765
366      1881   55,5574105    9,9613295
366      23550  55,5510846    9,9936818
367      1881   55,5574105    9,9613295
533      1881   55,5574105    9,9613295
533      23550  55,5510846    9,9936818
533      33550  55,6220936    10,0663895
533      33551  55,5573436    9,9611765
354      1881   55,5574105    9,9613295
354      23550  55.5510846    9,9936818
354      33550  55,6220936    10,0663895
354      33551  55,5573436    9,9611765

I like to have as a result the nearest ShopID for every CouponID

I already have a query:

SELECT CouponID, MIN (dbo.DistanceBetween (53.54613,9.98537,ShopLatitude,ShopLongitude)) as distance
FROM Table
GROUP BY CouponID
ORDER BY CouponID, distance ASC

which outputs the CouponID and the smallest distance to the ShopID for each coupon:

CouponID distance
354     0,778524633472375
365     0,778524633472375
366     0,778524633472375
367     2,02548179145764

In LINQ the statement looks like:

var coupon = (from c in dbContext.table
                      group c by c.CouponID into cgrp
                      select new
                      {
                        CouponID = cgrp.Key,
                        Distance = cgrp.Min(c => DistanceBetween(latitude, longitude, c.ShopLatitude, c.ShopLongitude))
                      })
                      .OrderBy(c => c.CouponID).ThenBy(c => DistanceBetween(latitude, longitude, c.ShopLatitude, c.ShopLongitude));

How can I also get back the related ShopID for the smallest distance from the query and Linq statement ?

Upvotes: 1

Views: 178

Answers (2)

GalacticJello
GalacticJello

Reputation: 11445

SELECT c.CouponID, c.ShopID, nearest.distance
FROM
Coupons C JOIN
(
    SELECT CouponID, MIN (dbo.DistanceBetween (53.54613,9.98537,ShopLat,ShopLon)) as distance
    FROM Coupons
    GROUP BY CouponID   
) nearest on c.CouponID = nearest.CouponID
          AND dbo.DistanceBetween (53.54613,9.98537,ShopLat,ShopLon) = nearest.distance
ORDER BY CouponID, distance ASC

Upvotes: 1

Roy Ashbrook
Roy Ashbrook

Reputation: 854

Just modify your sql query to perform the grouping and join you want there.

create a table to hold the distances, and then select the min where the couponid and shopid match.

Upvotes: 0

Related Questions