Solved

SQL Syntax

Posted on 2011-09-17
6
258 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

627 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