Solved

Query help to for min date with

Posted on 2011-02-10
7
730 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
Comment Utility
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
Comment Utility
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
Comment Utility
The GroupBy field is the one for which you will get the minimum date.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now