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
Solved

SQL Syntax 101

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

860 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