Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Syntax

Posted on 2011-09-17
6
Medium Priority
?
261 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
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
Independent Software Vendors: 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 31

Accepted Solution

by:
hnasr earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

927 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