Solved

SQL Syntax 101

Posted on 2011-09-17
5
296 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Finding Where Clause Value in SQL Views and SP 21 62
Shared Service Environment 2 54
T-SQL: Wrong Result 7 38
Does INTERSECT return opposite from FULL OUTER JOIN? 4 40
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

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