Re: how to make this query? Please help me

From: Craig Ledbetter <craigl_at_gte.net>
Date: Tue, 30 Nov 1999 08:20:46 GMT
Message-ID: <yNL04.1432$NW1.59402_at_dfiatx1-snr1.gtei.net>

It appears from your example that you do not want the results ordered by show name, either ascending or descending, but rather by the earliest date of a show. If this is correct, then the following query should give it to you. A subquery in the FROM clause finds the earliest date for each show, and by joining that result with the original two tables you get a result set that can be grouped by the earliest date for a show, but still ordered by show name and date. If two shows were ever first performed on the same date, the first show alphabetically would come first in the result set.

SELECT
   s.name ,
   d.date
FROM
   (SELECT

       showid ,
       MIN(date) firstdate
    FROM
       DATES
    GROUP BY
       showid

    ) fd ,
   shows s ,
   dates d
WHERE
    fd.showid = d.showid
   AND d.showid = s.id
ORDER BY
   fd.firstdate ,
   s.name,
   d.date

Hope this works for you,
Craig

Maiko wrote in message <383d8d55.802997_at_news2.cistron.nl>...
>I've 2 tables:
>
>shows and dates with the following contents:
>
>SHOWS:
>
>id name
> 1 showa
> 2 showb
> 3 showc
>
>DATES:
>
> id showid date
> 1 1 25-11-1999
> 2 1 30-11-1999
> 3 2 24-11-1999
> 4 2 27-11-1999
>
>
>
>
>We want the output of a query ordered by date and grouped by showname,
>ie
>
> showb 24-11-1999
> 27-11-1999
> showa 25-11-1999
> 30-11-1999
>
>
>Please help me out, and tell me how to do this in SQL. It's very
>urgent for me so any help will be very appreciated.
>
>Thanx in advance,
>
>Maiko (maikoo_at_cistron.nl)
Received on Tue Nov 30 1999 - 09:20:46 CET