Need help with a SQL query to find the ealiest date of a set of records

I'm trying to write a query, with no real luck to find the earliest instance of tblSession.Session_dtmShowing.  Everything I try from Min() to simply Group By and Sort By throws errors

SELECT     tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, tblSession.Session_dtmShowing
FROM         tblFilm INNER JOIN
                      tblSession ON tblFilm.Film_strCode = tblSession.Film_strCode

The results I'm looking for are
By Title, the opening date, the earliest showing (they can be different as the opening date may not be the same as the showing date

Should be grouped by Title.  If there's a way to get the total showings by title that would be useful too.

Any pointers would be appreicated.  Thank you
JCONNELLY626VP ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
If you order by tblSession.Session_dtmShowing, I suppose that's your showing date, in ascending order, what do you get?

SELECT     tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, tblSession.Session_dtmShowing
 FROM         tblFilm INNER JOIN
                       tblSession ON tblFilm.Film_strCode = tblSession.Film_strCode
                      group by  tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, tblSession.Session_dtmShowing
                      order by  tblSession.Session_dtmShowing ASC

Open in new window

0
PortletPaulfreelancerCommented:
I think you need to use row_number() which will give the earliest session (for each Film_strCode) a value of 1, then you filter for only those rows. Like this:
SELECT
      tblFilm.Film_strTitle
    , tblFilm.Film_dtmOpeningDate
    , derived.Session_dtmShowing
FROM tblFilm
      INNER JOIN (
                  SELECT
                        * -- please specify the fields
                      , ROW_NUMBER() OVER (PARTITION BY tblSession.Film_strCode
                                           ORDER BY tblSession.Session_dtmShowing ASC) AS rn
                  FROM tblSession
            ) AS derived
                  ON tblFilm.Film_strCode = derived.Film_strCode
                        AND derived.rn = 1

Open in new window

0
sammySeltzerCommented:
Also, min() should have worked for you.

SELECT     tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, MIN(tblSession.Session_dtmShowing) as earliestShowing
 FROM         tblFilm INNER JOIN
                       tblSession ON tblFilm.Film_strCode = tblSession.Film_strCode
                      group by  tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, tblSession.Session_dtmShowing

Open in new window


Finally, I think you can also use TOP clause

SELECT     TOP 1 tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, tblSession.Session_dtmShowing
 FROM         tblFilm INNER JOIN
                       tblSession ON tblFilm.Film_strCode = tblSession.Film_strCode
                      group by  tblFilm.Film_strTitle, tblFilm.Film_dtmOpeningDate, tblSession.Session_dtmShowing
                      order by  tblSession.Session_dtmShowing ASC

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JCONNELLY626VP ITAuthor Commented:
I got the MIn and the TOP to work.  Is there a way to output the results of the TOP, but for each title?  the earliest start for each title?

This site is my new best friend..

Thanks
0
awking00Commented:
select x.Film_strTitle, x.Film_dtmOpeningDate, x.Session_dtmShowing from
(SELECT f.Film_strTitle, f.Film_dtmOpeningDate, s.Session_dtmShowing,
        row_number() over (partition by f.Film_strTitle order by s.Session_dtmShowing) as rn
 FROM tblFilm as f INNER JOIN tblSession s ON f.Film_strCode = s.Film_strCode) as x
where x.rn = 1;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerCommented:
The min() should have worked, I think but try this:
;WITH partitioned AS (
    SELECT tblFilm.Film_strTitle, 
                 tblFilm.Film_dtmOpeningDate,
                tblSession.Session_dtmShowing
        ,ROW_NUMBER() OVER(PARTITION BY  tblFilm.Film_strTitle, tblSession.Session_dtmShowing
                            ORDER BY tblSession.Session_dtmShowing) AS seq
    FROM tblFilm INNER JOIN
                       tblSession ON tblFilm.Film_strCode = tblSession.Film_strCode
)
SELECT *
FROM partitioned WHERE seq = 1

Open in new window


Not tested of course but looks good to me.
0
JCONNELLY626VP ITAuthor Commented:
Looks good.  Thank you for all your suggestions.  Very appreciated
0
sammySeltzerCommented:
WOW!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.