Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax

Posted on 2011-09-17
6
Medium Priority
?
260 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
Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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. …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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