Solved

Query help to for min date with

Posted on 2011-02-10
7
768 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…

739 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