Solved

SQL Syntax 101

Posted on 2011-09-17
5
292 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
  • 3
5 Comments
 
LVL 59

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 59

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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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

911 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

19 Experts available now in Live!

Get 1:1 Help Now