Solved

SQL Syntax

Posted on 2011-09-17
6
254 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 17

Expert Comment

by:Garry-G
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need Distinct Value from Joined Tables in MSSQL 5 53
Oracle 10g - insert string with special characters 8 42
Optimizing a query 3 34
grouping logic 6 49
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now