Solved

SQL Syntax 101

Posted on 2011-09-17
5
297 Views
Last Modified: 2012-05-12
Attached is a small example of the database im working with

as you can see there can be several rrows for the same ITEM_NUMBER due to it having different ITEM_SUBSECTIONS and DATES

nevertheless each ITEM_NUMBER of 101 will always have the same SERIES and SERIES_SUBSECTION

I need the query syntax so that I get 1 full-row from each item_number that has the MIN_DATE under date
 
The excel sheet attached highlights it if im not communicting clearly
this is jsut a small same the master database has over 20,000 items in it


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
  • 3
5 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36555098
Hi.

You can probably do this via ROW_NUMBER() if I am understanding correctly and the issue is you cannot do a simple GROUP BY.

;WITH cte AS (
   SELECT {column list}
        , ROW_NUMBER() OVER(PARTITION BY ITEM_NUMBER ORDER BY DATES) RN
   FROM {your table}
)
SELECT {final column list}
FROM cte
WHERE RN = 1
;

Open in new window

0
 
LVL 22

Author Comment

by:plusone3055
ID: 36555226
no i think i need the GROUP by
I don't know  the row number out of 200,000 rows representing 1000's of records

the example database shows several highlighted records
a representation for
101                          
102                            
201
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36555233
Did you run the query I provided? If you use GROUP BY, you will have to aggregate on ALL the columns not involved in the grouping; therefore, you have to figure out if that is okay. If it is, then just determine the appropriate aggregate for each column. For date it is MIN(DATES) AS MIN_DATE.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36555435
Can you please share what else you needed other than this line?
ROW_NUMBER() OVER(PARTITION BY ITEM_NUMBER ORDER BY DATES) RN

Did it work or not?
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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