Solved

SQL Help -

Posted on 2016-11-22
12
59 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

930 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now