[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

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
0
JCONNELLY626
Asked:
JCONNELLY626
1 Solution
 
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
 
PortletPaulCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now