Get and Concatenate fields with Minimum value

[DIOS]

I have two tables that are related via a trip id like so:

Trips
-------------------------------------------------------------
TripId TripDate TripNumber Vehicle Driver
1 8/14/06 A200 MX3 Dave Jones
2 8/17/06 A450 DSW Dave Jones
3 8/19/07 C50A TR4 John Doe

Roads
-------------------------------------------------------------
TripId RoadId RoadNumber RoadDist RoadType
1 0 10A 200 HWY
1 1 16E 200 HWY
1 2 12X 210 ACC
2 3 1E 345 HWY
2 4 65 333 SVC
2 5 34R 312 HWY
3 6 10B 99 HWY
3 7 44 99 HWY
3 8 4S 99 SVC

For each trip I want to print out the minimum road distance and
associated roads. the output I need is something like

Trip TripNum MinDist Roads
1 A200 200 10A/16E
2 A450 312 34R
3 C50A 99 10B/44/4S

I can get the minimum distance but cant get the multiple records of
roads that meet that minimum distance in one shot. So far I have
something like

SELECT T.[TripId]
,T.[TripNumber]
,MinDist = (SELECT MIN(R.RoadDist) FROM [Roads] AS R WHERE
R.TripId = T.TripId)
,Roads = ??????
FROM [Trips] AS T
INNER JOIN [Roads] AS R ON (T.TripId=R.TripId)
ORDER BY T.TripNumber

Any help is appreciated. It may be simple but cant seem to find any
examples that will help me complete the SQL.

tia
AGP

[Plamen Ratchev]

Here is one solution:

WITH Ranked AS (
SELECT T.TripId, T.TripDate, T.TripNumber, R.RoadDist, R.RoadNumber,
DENSE_RANK() OVER(PARTITION BY R.TripId ORDER BY R.RoadDist) AS rk
FROM Trips AS T
JOIN Roads AS R
ON T.TripId = R.TripId)
SELECT DISTINCT TripId, TripDate, TripNumber, RoadDist,
STUFF((SELECT ',' + RoadNumber
FROM Ranked AS B
WHERE B.TripId = A.TripId
AND B.rk = 1
FOR XML PATH('')), 1, 1, '') AS roads
FROM Ranked AS A
WHERE rk = 1;

[DIOS]

Post by Plamen Ratchev
WITH Ranked AS (
SELECT T.TripId, T.TripDate, T.TripNumber, R.RoadDist, R.RoadNumber,
        DENSE_RANK() OVER(PARTITION BY R.TripId ORDER BY R.RoadDist) AS rk
FROM Trips AS T
JOIN Roads AS R
   ON T.TripId = R.TripId)
SELECT DISTINCT TripId, TripDate, TripNumber, RoadDist,
        STUFF((SELECT ',' + RoadNumber
               FROM Ranked AS B
               WHERE B.TripId = A.TripId
                 AND B.rk = 1
               FOR XML PATH('')), 1, 1, '') AS roads
FROM Ranked AS A
WHERE rk = 1;
--
Plamen Ratchevhttp://www.SQLStudio.com

ok let me study that a bit. I assume the STUFF is a keyword that joins
data.

AGP

[Plamen Ratchev]

STUFF is actually a built-in function to insert a string into another string:
http://technet.microsoft.com/en-us/library/ms188043.aspx

It is used here to remove the leading comma in the concatenated list (by replacing it with blank string). The solution
uses a common table expression (http://msdn.microsoft.com/en-us/library/ms175972.aspx) and the DENSE_RANK ranking
function (http://msdn.microsoft.com/en-us/library/ms173825.aspx) to create ranked result set based on minimal distance,
and then FOR XML PATH with blank element is used to concatenate to list.

[AGP]

Post by Plamen Ratchev
WITH Ranked AS (
SELECT T.TripId, T.TripDate, T.TripNumber, R.RoadDist, R.RoadNumber,
DENSE_RANK() OVER(PARTITION BY R.TripId ORDER BY R.RoadDist) AS rk
FROM Trips AS T
JOIN Roads AS R
ON T.TripId = R.TripId)
SELECT DISTINCT TripId, TripDate, TripNumber, RoadDist,
STUFF((SELECT ',' + RoadNumber
FROM Ranked AS B
WHERE B.TripId = A.TripId
AND B.rk = 1
FOR XML PATH('')), 1, 1, '') AS roads
FROM Ranked AS A
WHERE rk = 1;
--
Plamen Ratchevhttp://www.SQLStudio.com

Im gonna try to break this down and add it to my sql query. if there are any
other suggestions please let me know.

AGP

[DIOS]

Post by AGP

Post by Plamen Ratchev
WITH Ranked AS (
SELECT T.TripId, T.TripDate, T.TripNumber, R.RoadDist, R.RoadNumber,
DENSE_RANK() OVER(PARTITION BY R.TripId ORDER BY R.RoadDist) AS rk
FROM Trips AS T
JOIN Roads AS R
ON T.TripId = R.TripId)
SELECT DISTINCT TripId, TripDate, TripNumber, RoadDist,
STUFF((SELECT ',' + RoadNumber
FROM Ranked AS B
WHERE B.TripId = A.TripId
AND B.rk = 1
FOR XML PATH('')), 1, 1, '') AS roads
FROM Ranked AS A
WHERE rk = 1;
--
Plamen Ratchevhttp://www.SQLStudio.com

Im gonna try to break this down and add it to my sql query. if there are any
other suggestions please let me know.
AGP

Taking the suggestions, here is what I ended up doing and it seems to
work. I'm going to spot check the results against the raw data to
confirm that it's doing what I want it to do. I basically use a SELECT
within a SELECT to get a recordset of those roads that meet the
minimum distance. I then use the FOR XML PATH to concatenate the list.
Then replace the spaces with my chosen delimiter. I've done the
tacking of a space at the end of each road number because my data is
actually more than two fields combined. Anyways, this seems to work
for me.

SELECT T.[TripId]
,T.[TripNumber]
,MinDist = (SELECT MIN(R.RoadDist) FROM [Roads] AS R WHERE
R.TripId = T.TripId)
,Roads = REPLACE( RTRIM( (SELECT R.RoadNumber + ' ' FROM [Roads]
AS R WHERE T.TripId=R.TripId AND R.RoadDist = (SELECT MIN(R.RoadDist)
FROM [Roads] AS R WHERE T.TripId=R.TripId) FOR XML PATH('')) ) , ' ',
'\')
FROM [Trips] AS T
INNER JOIN [Roads] AS R ON (T.TripId=R.TripId)
ORDER BY T.TripNumber

Thanks for the help.
AGP

[--CELKO--]

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.

[AGP]

Post by --CELKO--
Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are the foundation of RDBMS, after
all.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.

mmmm.....have no idea what this means and how it applies to my problem.

agp

[Tony Rogerson]

Stop thinking SQL Server is just a store and retrieve product - it isn't.

It's a data processing engine and as such manipulation like this is better
done inside the database where it is significantly more efficient.

Post by --CELKO--
Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are the foundation of RDBMS, after
all.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.

[DIOS]

Post by Tony Rogerson
Stop thinking SQL Server is just a store and retrieve product - it isn't.
It's a data processing engine and as such manipulation like this is better
done inside the database where it is significantly more efficient.

Post by --CELKO--
Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure?  Normal forms are the foundation of RDBMS, after
all.
Why are you formatting data in the back end?  The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end.  This is a more basic programming principle than just
SQL and RDBMS.
Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.

That seems like a plausible suggestion. however, i like many others
are simply users of the database and have no control at all as to what
goes into the database and how it is structured. In my case, all I
have is read access to the db and have to construct a report from the
data.

AGP