Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax 101

Posted on 2011-09-17
5
Medium Priority
?
300 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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 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