Solved

SQL Syntax

Posted on 2011-09-17
6
257 Views
Last Modified: 2012-05-12
im working with a data base that has 1000's of records

attached is a sample of the data

an below the samepl data in the excel sheet is the desired output from the query i need

I need to pull each record that has the MIN DATE under its Date  
the table name is MACHINEITEM


Example-Database.xls
0
Comment
Question by:plusone3055
[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
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36555502
Select * from yourtable t1 inner join
   (select item_number, min([date]) as [date] from yourtable group by item_number) t2
   on t2.[date] = t1.[date] and t2.item_number = t1.item_number
0
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36555506
Something like this ought to do: (apart from the fact that the "DATE" field most likely has to be renamed due to being a keyword)

select ITEM_NUMBER, ITEM_SUBSECTION, SERIES, SERIES_SUBSECTION, MIN(DATE) from MYDATATABLE group by ITEM_NUMBER order by ITEM_NUMBER;
0
 
LVL 22

Author Comment

by:plusone3055
ID: 36555513
DGMG
yours returend only 3 records when there should have been 7 out of that sample data

GARRY-G
your query returned this error

Msg 8120, Level 16, State 1, Line 1
Column 'milestone.ITEM_SUBSECTION' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:nishant joshi
ID: 36555649
select distinct ITEM_NUMBER, ITEM_SUBSECTION, SERIES, SERIES_SUBSECTION, DATE from MYDATATABLE order by Date;
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 36555652
Try this using access 2007:

SELECT a.*
FROM myData a INNER JOIN (SELECT ITEM_NUMBER, MIN(DAte) as minDate FROM myData GROUP BY ITEM_NUMBER) b
ON a.ITEM_NUMBER=b.ITEM_NUMBER AND a.Date = b.minDate

ITEM_NUMBER      ITEM_SUBSECTION      SERIES      SERIES_SUBSECTION      DATE
101      9672593      1      1      09/02/2001
102      9672598      1      1      01/01/2001
201      9672605      1      1      28/06/1975
211      9742489      1      1      07/08/2000
301      9672603      1      1      09/02/1981
410      9672558      1      1      25/12/1998
510      9672557      1      1      15/03/1981
0
 
LVL 22

Author Closing Comment

by:plusone3055
ID: 36556452
Got It
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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