The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Course Of The Month
5 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to use TOP 1 in a T-SQL sub-query?
Posted on 2016-10-25
Can someone help me with the correct syntax for a T-SQL query I am trying to apply in a SQL Server 2005 database?
I have TABLE_A that contains unique Order Items and a View VW_B that might contain multipe instances of items from TABLE_A along with further columns concerning the Invoices these Order Items might have appeared on.
I would like to create a query that selects each item from TABLE_A and details of only a single instance of them from VW_B (the latest Invoiced they featured on).
I have been trying suggestions from other web-sites but either get syntax errors or only a single row of output.
My latest attempt has been along the lines of :
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
OUTER APPLY
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
ORDER BY VB.INVDATE DESC
WHERE VB.ITEMID = A.ITEMID) AS B
The above format produces a syntax error with the keyword "WHERE" on Line 7.
Is it because I am using a View rather than a Table in my "SELECT TOP 1 ... " sub-query?
I hope someone can understand what I am trying to do and show me where I am going wrong.
Thanks & regards.
Colin.
I have TABLE_A that contains unique Order Items and a View VW_B that might contain multipe instances of items from TABLE_A along with further columns concerning the Invoices these Order Items might have appeared on.
I would like to create a query that selects each item from TABLE_A and details of only a single instance of them from VW_B (the latest Invoiced they featured on).
I have been trying suggestions from other web-sites but either get syntax errors or only a single row of output.
My latest attempt has been along the lines of :
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
OUTER APPLY
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
ORDER BY VB.INVDATE DESC
WHERE VB.ITEMID = A.ITEMID) AS B
The above format produces a syntax error with the keyword "WHERE" on Line 7.
Is it because I am using a View rather than a Table in my "SELECT TOP 1 ... " sub-query?
I hope someone can understand what I am trying to do and show me where I am going wrong.
Thanks & regards.
Colin.
[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
3
3- +1
14 Comments
I think it's the ORDER BY in the subquery. I've never been able to have an ORDER BY in a subquery. I'm surprised that didn't show up directly as the problem.
Do the ORDER BY after the final query is created. Does that help?
Do the ORDER BY after the final query is created. Does that help?
Thanks for the fast response; I'll try that out now.
Also, I think I made a slight mistake when I was paraphrasing my more meaningful table and column names into the simplified format I gave in my example.
I think the WHERE line should have preceded the ORDER BY line in my sub-query.
Regards.
Also, I think I made a slight mistake when I was paraphrasing my more meaningful table and column names into the simplified format I gave in my example.
I think the WHERE line should have preceded the ORDER BY line in my sub-query.
Regards.
Hello again.
Yes, I am reading elsewhere that "ORDER BY" is not valid in a sub-query.
I am obviously going about this the wrong way because I do need my sub-query (or some other data extraction method) to do some sort of ordering because I want to find the single row from my View that identifies the most recent Invoice that an Item featured in.
I would have thought that my View needed to be sorted into "Item, InvDate DESC" order so that I could then pick the "TOP 1" row for each of my items to give me their most recent Invoice Date.
Can anyone suggest another strategy rather than my failed sub-query attempts?
Many thanks.
Yes, I am reading elsewhere that "ORDER BY" is not valid in a sub-query.
I am obviously going about this the wrong way because I do need my sub-query (or some other data extraction method) to do some sort of ordering because I want to find the single row from my View that identifies the most recent Invoice that an Item featured in.
I would have thought that my View needed to be sorted into "Item, InvDate DESC" order so that I could then pick the "TOP 1" row for each of my items to give me their most recent Invoice Date.
Can anyone suggest another strategy rather than my failed sub-query attempts?
Many thanks.
Yes, you are correct about the WHERE and ORDER BY locations. Still, take it out of the subquery 'cause I've never been able to get away with that in my own queries.
I see you're doing the top 1 and then ordering by date and hence that's why you want the order by. You can do a similar thing by selecting the max date.
So, do this:
Anyway, I might not have it exact because I tend to try these things and tweak them to work as I work on them. You should be getting the idea here though. You want yet another subquery that gets the max date from the same table for that item, then use that max date to find the item with the max date.
Therefore, if you have like 10 dates, you get the max data for that ID then get the row with that max date which returns the row you want and is essentially the same as the TOP 1 with order by DESC.
I see you're doing the top 1 and then ordering by date and hence that's why you want the order by. You can do a similar thing by selecting the max date.
So, do this:
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
OUTER APPLY
(
SELECT
VB.INVREF,
VB.INVDATE
FROM VW_B AS VB
WHERE VB.ITEMID = A.ITEMID
AND VB.INVDATE = (SELECT MAX(INVDATE) FROM VW_B WHERE INVREF = VB.INVREF)
) AS B
Anyway, I might not have it exact because I tend to try these things and tweak them to work as I work on them. You should be getting the idea here though. You want yet another subquery that gets the max date from the same table for that item, then use that max date to find the item with the max date.
Therefore, if you have like 10 dates, you get the max data for that ID then get the row with that max date which returns the row you want and is essentially the same as the TOP 1 with order by DESC.
Heh. I was typing up the answer to your question as you asked it. If you can think a valid set of data that represents what you have, we can do some test queries.
Active today
Try..
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
OUTER APPLY
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
WHERE VB.ITEMID = A.ITEMID
ORDER BY VB.INVDATE DESC ) AS B
Active today
The ORDER BY clause is always the last one in a query. Pawan got it right, here is formatted:
SELECT
A.Col1,
A.Col2,
B.*
FROM
TABLE_A AS A
OUTER APPLY
(
SELECT TOP 1
VB.INVREF,
VB.INVDATE
FROM
VW_B AS VB
WHERE
VB.ITEMID = A.ITEMID
ORDER BY
VB.INVDATE DESC
) AS B
Thanks very much for the further suggestions.
I won't be able to work on this again until tomorrow afternoon (UK time), but I will report back then with my progress.
Best regards.
I won't be able to work on this again until tomorrow afternoon (UK time), but I will report back then with my progress.
Best regards.
Thanks for the suggestions, which I have just been able to test before rushing off again.
I have 7,820 items in Table_A
I have on 52 items in View_B
Not all items from Table_A occur in View_B but some if the items in View_B are duplicated because they have recently been invoiced more than once and I only want the most recent instances from View_B.
Daniel's suggestion gives me 7,843 results!
Both Pawan and Zberteoc's suggestions give 7,820 results.
I am hoping to get 52 or fewer results. I only want the items from Table_A that are also generated by View_B.
I might try breaking things down into a couple of separate Views rather than trying to achieve my result in a singe (obviously trick) SQL command.
Any further suggestions welcomed, though.
Best regards. Colin.
I have 7,820 items in Table_A
I have on 52 items in View_B
Not all items from Table_A occur in View_B but some if the items in View_B are duplicated because they have recently been invoiced more than once and I only want the most recent instances from View_B.
Daniel's suggestion gives me 7,843 results!
Both Pawan and Zberteoc's suggestions give 7,820 results.
I am hoping to get 52 or fewer results. I only want the items from Table_A that are also generated by View_B.
I might try breaking things down into a couple of separate Views rather than trying to achieve my result in a singe (obviously trick) SQL command.
Any further suggestions welcomed, though.
Best regards. Colin.
Active today
try..
CROSS APPLY
CROSS APPLY
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
CROSS APPLY
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
WHERE VB.ITEMID = A.ITEMID
ORDER BY VB.INVDATE DESC ) AS B
Active today
Replace:
OUTER APPLY
with
CROSS APPLY
OUTER APPLY is similar to LEFT JOIN while CROSS APPLY to INNER JOIN.
OUTER APPLY
with
CROSS APPLY
OUTER APPLY is similar to LEFT JOIN while CROSS APPLY to INNER JOIN.
Thanks for that.
The CROSS APPLY seems to do the job, reducing my results down from 7,820 possible items, through 52 recent invoice events, to the specific 29 items involved in these invoices.
I will double-check that it is producing the correct invoicing event for multi-invoiced items, but I think I now have a mechanism I can work.
I think this is the first time I have used the APPLY feature so will need to read a bit more to get my head around it.
Many thanks to everyone who commented.
The CROSS APPLY seems to do the job, reducing my results down from 7,820 possible items, through 52 recent invoice events, to the specific 29 items involved in these invoices.
I will double-check that it is producing the correct invoicing event for multi-invoiced items, but I think I now have a mechanism I can work.
I think this is the first time I have used the APPLY feature so will need to read a bit more to get my head around it.
Many thanks to everyone who commented.
Featured Post
Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
INTRODUCTION:
While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month5 days, 22 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.