Solved

Query help to for min date with

Posted on 2011-02-10
7
774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 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