• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

Query help to for min date with

I have one table ...  (simplified here) ....   3 columns:
PLOT_VISIT_CN           PLOT              DATE
        1                        Big_Sagen          1/1/1997
        2                        Big_Sagen          2/3/2001
        3                        Little_Boy            1/1/1997
        4                        Little_Boy          7/23/2002
        5                        Little_Boy          8/24/2003

I'm trying to write a query to show the minimum date but I need all three fields.
When using only Plot and Min(Date) ...  looks good, but when add PLOT_VISIT_CN the rows are unique so I get them all.

Trying to join the table on itself without much luck.  I should know how to do this but cannot get it to work ...   appreciate any help.
Thanks
0
DoveTails
Asked:
DoveTails
  • 2
  • 2
  • 2
  • +1
1 Solution
 
IceCodeCommented:
Can I see one of the queries that doesn't work and maybe an example of what you expect the output to look like please?
0
 
Helen FeddemaCommented:
The question is, minimum date for what?  If you add PLOT_VISIT_CN, you will get the minimum date for that ID, which is to say, all records (one date per ID).
0
 
Helen FeddemaCommented:
The GroupBy field is the one for which you will get the minimum date.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Rey Obrero (Capricorn1)Commented:

select a.*
from table as a
inner join
(select min(b.[date]) as minDate, PLOT_VISIT_CN
 from table as b
 group by PLOT_VISIT_CN
) as c
on a.[date]=c.[minDate] and a.PLOT_VISIT_CN=c.PLOT_VISIT_CN
0
 
DoveTailsAuthor Commented:
Thanks for the quick responses....I'll share some more info.  

I'm after the earliest date for the PLOT, but I also need the PLOT_VISIT_CN along with it.
End results would be
PLOT_VISIT_CN           PLOT              DATE
        1                        Big_Sagen          1/1/1997
        3                        Little_Boy            1/1/1997

This query returns the correct data but does not contain the plot_visit_cn:
   SELECT Min(plot_visits.DATE) AS MinOfDATE, plot_visits.PLOT
   FROM plot_visits
   GROUP BY plot_visits.PLOT;

When I add the plot_visit_cn to the query as follows...i'm getting too many results, as Helen pointed out, i'm getting the earliest date for the PLOT_VISIT_CN.... and I want earliest for PLOT.
   SELECT plot_visits.PLOT_VISIT_CN, plot_visits.PLOT, Min(plot_visits.Date) AS MinOfDATE
   FROM plot_visits
   GROUP BY plot_visits.PLOT_VISIT_CN, plot_visits.PLOT;

and thanks Capricorn1 though the query is returning by PLOT_VISIT_CN also....I've been trying similar queries and think that's the way to go.
I'm still trying.....
0
 
Rey Obrero (Capricorn1)Commented:
try this


select a.*
from plot_visits as a
inner join
(select min(b.[date]) as minDate, b.PLOT_VISIT_CN
 from plot_visits as b
 group by b.PLOT_VISIT_CN
) as c
on a.[date]=c.[minDate] and a.PLOT_VISIT_CN=c.PLOT_VISIT_CN
0
 
DoveTailsAuthor Commented:
Thanks to everyone for helping out and a special thanks to Capricorn1  .... I needed to modify the final suggested query but it helped me understand what to do.  Here's my final query:
SELECT a.plot_visit_cn, a.plot, a.date
FROM plot_visits AS a
INNER JOIN
   (select min(b.[date]) as minDate, PLOT
    from plot_visits as b
    group by PLOT
    )  AS c ON (a.[date] = c.[minDate]) AND (a.PLOT = c.PLOT);

Thanks again !!!!
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now