Return 2 entries per group based on the two lowest values in group

Below is a query that I need to modify so that it returns just the 2
MastNUMs, Stores, and Distances where the Distances are the two lowest for a
mastnum. So the results would be more like this
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000068 76 4.447685
000000068 70 5.516853
000000069 70 3.836682
000000069 76 6.331691
000000070 76 3.729323
SELECT TOP 15 MastNUM, Store, Distance
FROM DistTest
GROUP BY MastNUM, Store, Distance
ORDER BY MastNUM, Distance
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000067 69 8.112116
000000067 112 19.924702
000000068 76 4.447685
000000068 70 5.516853
000000068 69 6.874022
000000068 112 18.622366
000000068 71 19.396528
000000069 70 3.836682
000000069 76 6.331691
000000069 69 8.924779
000000069 112 16.709897
000000069 71 17.462224
000000070 76 3.729323
--
KoryI can get the lowest for each with the following but how can I get the
lowest 2?
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||I think I have found a way to get the lowest and 2nd lowest, but I hope
their is a better way than the following..
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
(
SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest t2
WHERE Distance > (
SELECT MIN(Distance)
FROM DistTest d2
WHERE t2.MastNum = d2.MastNum
GROUP BY MastNum
)
GROUP BY MastNum
) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
order by T1.mASTnUM
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:51 AM CDT ] [Execution: 16/ms]
MastNUM Store Distance
-- -- --
000000067 70 7.082444
000000068 70 5.516853
000000069 76 6.331691
000000070 70 6.957127
000000071 70 5.944537
000000072 76 6.277529
000000073 70 5.487792
000000074 70 7.193161
000000076 70 5.201532
000000077 70 5.412091
000000078 76 5.295454
000000079 76 5.395715
000000080 76 5.950446
000000081 70 5.920339
000000082 70 5.529576
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:52 AM CDT ] [Execution: 93/ms]
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:uVgDQB9WDHA.2268@.TK2MSFTNGP11.phx.gbl...
> I can get the lowest for each with the following but how can I get the
> lowest 2?
> SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
> FROM DistTest t1,
> ( SELECT MastNUM, MIN(Distance) AS MinDist
> FROM DistTest
> GROUP BY MastNum ) as Dmin
> WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
> go
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000068 76 4.447685
> 000000069 70 3.836682
> 000000070 76 3.729323
> 000000071 76 4.046238
> 000000072 70 3.928709
> 000000073 76 4.663551
> 000000074 76 3.206388
> 000000076 76 4.745636
> 000000077 76 5.338428
> 000000078 70 5.121837
> 000000079 70 4.580213
> 000000080 70 4.338181
> 000000081 76 4.069455
> 000000082 76 4.465975
>
> "Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
> news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> > Below is a query that I need to modify so that it returns just the 2
> > MastNUMs, Stores, and Distances where the Distances are the two lowest
for
> a
> > mastnum. So the results would be more like this
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000070 76 3.729323
> >
> > SELECT TOP 15 MastNUM, Store, Distance
> > FROM DistTest
> > GROUP BY MastNUM, Store, Distance
> > ORDER BY MastNUM, Distance
> >
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000067 69 8.112116
> > 000000067 112 19.924702
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000068 69 6.874022
> > 000000068 112 18.622366
> > 000000068 71 19.396528
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000069 69 8.924779
> > 000000069 112 16.709897
> > 000000069 71 17.462224
> > 000000070 76 3.729323
> >
> > --
> >
> > Kory
> >
> >
>|||The initial step creating this list is as follows:
SELECT g.MASTNUM, s.STORE, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG ) AS Distance
INTO DistTest
FROM geomailing g, geoStore s
WHERE master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) <= 20
GROUP BY g.Mastnum, s.Store, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG )
ORDER BY g.Mastnum, master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG )
Can anyone suggest a way that it only inserts into this table the 2 lowest
Distances ( master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) ) ? So
as to avoid having to pull out the two lowest distances later?
Thanks.
Kory wrote in message news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||Does anyone have any suggestions on how to get just the 2 lowest values
grouped?|||I would imagine a lot of people have a lot of ideas but with what you have
given us it would be stabbing in the dark.
1. Can you post a simple table structure
2. Post sample data to enter in
3. Tell us what you expect to see as the end result.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:OPqoHUDXDHA.208@.tk2msftngp13.phx.gbl...
> Does anyone have any suggestions on how to get just the 2 lowest values
> grouped?
>