Solved

SQL Help -

Posted on 2016-11-22
12
71 Views
Last Modified: 2016-12-28
I have a table of equipment, say just ID column and description.  Then a related table of work orders  - ID column, description, date of work, and FK to equipment.   One to many.     I need a query that pulls the 5 most recent work orders by date for each equipment record.
0
Comment
Question by:HLRosenberger
[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
  • 6
  • 4
  • 2
12 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 41898000
It's a simple JOIN:

SELECT TOP 5 *
FROM WorkOrders WO 
    INNER JOIN Equipment E ON E.ID = WO.Equipment ID
ORDER BY WO.WorkDate DESC;

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898002
Wont that just give me the top 5 across ALL equipment record?   I want the top 5 per unique equipment record.
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41898022
Ah, didn't read that part.. What SQL Server version?

WITH Ordered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY E.ID ORDER BY WO.WorkDate DESC) AS RN
    FROM WorkOrders WO 
        INNER JOIN Equipment E ON E.ID = WO.Equipment ID
    )
    SELECT *
    FROM Ordered
    WHERE RN < 6;

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898025
I know how to do this using a Stored Proc.   I could loop through all equipment records and do a query to pull the TOP 5 work order records by date, save them to a temp table, them return that temp table.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898026
2008
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898030
Does 2008 support that syntax?
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41898032
Then it will work. ROW_NUMBER is part of T-SQL since SQL Server 2008.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898037
Below is my real SQL.  I remove the date column in ORDER BY because that's actually in a related table - I need to add another JOIN.  I'm getting this error:

The column 'date_last_updated' was specified multiple times for 'Ordered'.



WITH Ordered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY E.dep_ID ORDER BY WO.id DESC) AS RN
    FROM Work_Order WO
        INNER JOIN deprec E ON E.dep_ID = WO.equipment_id
    )
    SELECT *
    FROM Ordered
    WHERE RN < 6;
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41898132
I need to add another JOIN.  I'm getting this error:

The column 'date_last_updated' was specified multiple times for 'Ordered'.

You MUST use either the tablename or a table alias when specifying any column. This error is due to the fact that, now you have joined multiple tables, that at least 2 of those tables contain [date_last_updated] as columns. So you need to refer to a.[date_last_updated] or b.[date_last_updated] where a & b refer to your tables or aliases. You should use this style of referencing throughout the query - no exceptions.

Never use "select *" in stored procedures or views. We use it in our answers as a shorthand for: "we don't know the details of your tables, so you have to fill in this part". i.e. just because we use it in answers does not mean we recommend it for production use.

I'm afraid you are missing the point about ROW_NUMBER() and OVER(). It is absolutely ESSENTIAL that you use ORDER BY  a date/time column inside the OVER clause. To meet your stated requirement this simply isn't optional.  ROW_NUMBER() just generates unique integers but it does this according to the rules as specified through the OVER clause. The PARTITION BY is the grouping of data and the ORDER BY inside the OVER arranges the rows withing the partition so you know which row will get a value of 1,2,3,4,5 etc. Here we NEED to order by a date in DESCending order to get the "the 5 most recent work orders by date for each equipment record".

Also, I wish folks wouldn't (ab)use common tables expressions when there is zero advantage in using them. There is no reason to use "with ... as ()" in this query listed on this page so I suggest you don't use that approach. A simple "derived table" achieves the objective here.

Lastly you state you objective as "the 5 most recent work orders by date for each equipment record"

So: don't you need to list ALL items of equipment? and then only list UP TO 5 work orders (arranged by date descending). Imagine if an item of equipment has not been used on any work order. I think you need to reverse the precedence of the tables and use the equipment table in the FROM clause with a LEFT JOIN to work orders.

[please remember I am not recommending "select *"  you have to provide the details]
SELECT
      d.dep_id, d.last_updated_date
FROM (
      SELECT
            E.dep_ID
          , WO.last_updated_date 
          -- specify the other columns you need
          , ROW_NUMBER() OVER (PARTITION BY E.dep_ID ORDER BY WO.last_updated_date DESC) AS RN
      FROM deprec E 
      LEFT JOIN Work_Order WO ON E.dep_ID = WO.equipment_id
      ) d
WHERE d.RN < 6

Open in new window

0
 
LVL 34

Expert Comment

by:ste5an
ID: 41898160
Also, I wish folks wouldn't (ab)use common tables expressions when there is zero advantage in using them.

Well, there is an obvious advantage: It increases readability. And a CTE in this case creates the same execution plan, so no disadvantages. And last but least: Creating optimal SQL statements is query optimizing and and DBA task. The question here is clearly a development issues. I strongly encourage to separate those two roles in SQL development and implementation. This avoids premature optimization, which we know is the root of all evil..
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41898187
Readability is subjective.

If you approach sql as a procedural language then subjectively you improve readability. But SQL is not procedural.

There is zero advantage to using a cte here.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41906277
Thanks!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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