Solved

Query help to for min date with

Posted on 2011-02-10
7
750 Views
Last Modified: 2012-08-13
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
Comment
Question by:DoveTails
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 4

Expert Comment

by:IceCode
ID: 34864483
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34864488
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34864497
The GroupBy field is the one for which you will get the minimum date.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34864512

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

Author Comment

by:DoveTails
ID: 34864977
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34865048
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
 
LVL 5

Author Closing Comment

by:DoveTails
ID: 34865224
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 76
SSRS 2013 - Creating a summarized report 19 33
SYbase 4 29
is Microsoft Access going to Die? 9 45
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question