Sorting Problem. Please Help

[tmahoney]

I am having a problem showing a set of data that I have got from an sql statement:

SELECT
T.Code TravelNumber,
T.Start TravelDate,
T.QuoteID PackageID
FROM Travel T
INNER JOIN Package P ON P.ID = T.QuoteID
WHERE T.CODE LIKE 'KM%' --Bus Code
AND T.Start > GETDATE() --Future Departure Dates Only
AND T.TID=302 --Type is Bus
AND P.SID <> 2560 --Status is Confirmed
ORDER BY T.QuoteID ASC

As an Example the following returns:

TravelNumber | TravelDate | PackageID
KM001 | 01/01/2007 | 1
KM002 | 07/01/2007 | 1
KM005 | 01/05/2007 | 2
KM006 | 07/05/2007 | 2
KM055 | 01/03/2007 | 3
KM056 | 07/03/2007 | 3


What I need is the data to be grouped by packageID but in order of the 1st Travel Date. As you can probably guess, the 1st Date of the package is out, and the 2nd is return.

So it needs to look like this:

TravelNumber | TravelDate | PackageID
KM001 | 01/01/2007 | 1
KM002 | 07/01/2007 | 1
KM055 | 01/03/2007 | 3
KM056 | 07/03/2007 | 3
KM005 | 01/05/2007 | 2
KM006 | 07/05/2007 | 2

I've tried so many thing to keep it simple but group by does not seem to apply here and a basic sort doesnt do anything.

Just in case it helps, there is a field in the travel table that says if the row is a departure travel. e.g. Out =1.

Please can someone shed some light on this little problem

Thanks

 

[tmahoney]

One other thing... why it cannot be by travel date:

Eventually Travel dates will overlap allowing inconsistent data

 

[carp]

tmahoney -
I have to admit - I don't know an ANSI Standard way to do this, so I have come up with an Oracle-based solution. As long as your RDBMS can handle inline views and has a counterpart to Oracle's ROWNUM, this might get you to where you need to be. Also note that I have substituted your query with a table I named mahoney in order to keep the approach clear; if this approach works for you, just replace each occurrence of "mahoney" with your query.

SELECT m.* from mahoney m, 
               (SELECT rownum ooa, v1.* 
                  FROM (SELECT min(tdate), tid 
                          FROM mahoney 
                         GROUP BY tid 
                         ORDER BY 1
                        ) v1
                ) v2
WHERE m.tid = v2.tid
ORDER BY v2.ooa, m.tdate;

This produces the following:

11:58:02 SQL> select * from mahoney;

TNUMBER    TDATE            TID
---------- --------- ----------
KM001      01-JAN-07          1
KM002      01-JUL-07          1
KM005      05-JAN-07          2
KM006      05-JUL-07          2
KM055      03-JAN-07          3
KM056      03-JUL-07          3

6 rows selected.

11:58:21 SQL> SELECT m.* from mahoney m,
11:59:33   2                 (SELECT rownum ooa, v1.*
11:59:33   3                    FROM (SELECT min(tdate), tid
11:59:33   4                            FROM mahoney
11:59:33   5                           GROUP BY tid
11:59:33   6                           ORDER BY 1
11:59:33   7                          ) v1
11:59:33   8                  ) v2
11:59:33   9  WHERE m.tid = v2.tid
11:59:33  10  ORDER BY v2.ooa, m.tdate;

TNUMBER    TDATE            TID
---------- --------- ----------
KM001      01-JAN-07          1
KM002      01-JUL-07          1
KM055      03-JAN-07          3
KM056      03-JUL-07          3
KM005      05-JAN-07          2
KM006      05-JUL-07          2

6 rows selected.

Of course, this means we're running your query twice, but this is the only solution I can think of just now.

 

[r937]

untested --

SELECT T.Code    AS TravelNumber
     , T.Start   AS TravelDate
     , T.QuoteID AS PackageID
     , dates.min_date
  FROM Travel T 
INNER 
  JOIN Package P 
    ON P.ID = T.QuoteID
   AND P.SID <> 2560 --Status is Confirmed
INNER
  JOIN (
       SELECT QuoteID
            , MIN(Start) as min_date
         FROM Travel
       GROUP
           BY QuoteID
       ) as dates
    ON dates.QuoteID = T.QuoteID 
 WHERE T.CODE LIKE 'KM%' --Bus Code
   AND T.Start > GETDATE() --Future Departure Dates Only
   AND T.TID = 302 --Type is Bus
ORDER 
    BY dates.min_date
     , T.QuoteID ASC

r937.com | rudy.ca

 

[tmahoney]

Thank you both so much for your help. R937's worked perfectly although I didnt try carp's but It will come in handy for another query I needed.

Cheers