Solved

SQL Help -

Posted on 2016-11-22
12
68 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 33

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 33

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 33

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 33

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 61
Loop through SQL parameters and insert to temp table? 4 52
SQL - Curser to do an insert based on a select 2 24
SQL Select Query help 1 34
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 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