November 3, 2013 at 5:03 pm
Hello all,
I have a query I am working on where an orders table has a version column for each line in the order and the order number is a column as well. I am trying to retrieve the line numbers of the orders but only showing their last version. I tried it using TOP 1 and MAX function but it isn't working.
Can someone suggest a way to get the desired result set?
Below is a sample of the DDL
CREATE TABLE [dbo].[tempSalesOrder]
(
[DocumentNum] [varchar](20) NOT NULL,
[LineNum] [int] NOT NULL,
[VersionNum] [int] NOT NULL,
[CustomerNum] [varchar](20) NOT NULL,
)
GO
insert into [dbo].[tempSalesOrder]
(
[DocumentNum],[LineNum], [VersionNum], [CustomerNum]
)
Values
( 'SO-1234', '1', '1', '108')
,( 'SO-1234', '1', '2', '108')
,( 'SO-1234', '2', '1', '108')
,( 'SO-1234', '2', '2', '108')
,( 'SO-1234', '2', '3', '108')
,( 'SO-1234', '2', '4', '108')
,( 'SO-1234', '3', '1', '108')
Select * from [dbo].[tempSalesOrder];
Below is a sample of the desired result set
[DocumentNum],[LineNum], [VersionNum], [CustomerNum]
SO-1234 1 2 108
SO-1234 2 4 108
SO-1234 3 1 108
Even though I have 7 records for the particular order I only want to see the last version which would give me 3 records.
Any suggestions would be appreciated.
Thanks.
November 3, 2013 at 5:29 pm
Try this:
SELECT DocumentNum, LineNum, CustomerNum, MAX(VersionNum) AS MaxVersion
FROM tempSalesOrder
GROUP BY DocumentNum, LineNum, CustomerNum;
November 3, 2013 at 6:02 pm
Depending on what you want to retrieve from the selected row, you may want to consider doing it like this:
SELECT DocumentNum, LineNum, VersionNum, CustomerNum
FROM
(
SELECT DocumentNum, LineNum, VersionNum, CustomerNum
,rn=ROW_NUMBER() OVER (PARTITION BY DocumentNum, LineNum ORDER BY VersionNum DESC)
FROM tempSalesOrder
) a
WHERE rn=1;
For example, suppose the CustomerNum changed on the last revision. In that case I think this would work but PietLinden's might not. I'm saying this because presumably when there is a change to the line (new revision), there must be something changing. It is just unclear whether you need to return the current value along with it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 9, 2013 at 1:44 pm
Dwain and Piet,
both of your suggestions work very well. thanks for the reply. I can't believe I missed your suggestion Piet. I was playing with a similar query and it just never occurred to me to drop the aggregate function off the group by clause.
Dwain I also like your suggestion and didn't think a subquery would work but you showed me it can. Thanks.
November 10, 2013 at 5:22 pm
kwoznica (11/9/2013)
Dwain and Piet,both of your suggestions work very well. thanks for the reply. I can't believe I missed your suggestion Piet. I was playing with a similar query and it just never occurred to me to drop the aggregate function off the group by clause.
Dwain I also like your suggestion and didn't think a subquery would work but you showed me it can. Thanks.
Technically what I provided contains a derived table and not a subquery.
But the important thing is that you find something that works for you.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 11, 2013 at 9:10 am
Your tables - they have no indexes, particularly no UNIQUE indexes.
As the above posters have said, it's essentially a two-step process:
1) Find the <uniqueish row identifier>, MAX(VersionNum) GROUP BY <uniqueish row identifier>
2) JOIN back to the main table.
I'd recommend pulling up Profiler, watch the CPU, Read, Write, and Duration columns of the SQL:BatchCompleted event, and trying at least the following variations, on a set large enough to be your production set as of two years from now, assuming good growth:
#temp table, no idexing
#temp table with (UNIQUE) indexing (optional: variants on when to put in the indexes, variants on what kind of indexing, variants on the ORDER BY during the initial insert, etc.)
derived table (as provided above)
CTI (optional)
Come up with your own alternate (left as an exercise to the reader)
You can also experiment with different column orders in the index(es) on your source table - keep in mind overall performance of DML and other SELECT statements, though.
November 11, 2013 at 5:16 pm
Nadrek (11/11/2013)
As the above posters have said, it's essentially a two-step process:
1) Find the <uniqueish row identifier>, MAX(VersionNum) GROUP BY <uniqueish row identifier>
2) JOIN back to the main table.
I certainly did not suggest that a "JOIN back to the main table" was needed.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 12, 2013 at 10:09 am
dwain.c (11/11/2013)
I certainly did not suggest that a "JOIN back to the main table" was needed.
You are correct - my apologies. I'm used to having to first find the MIN/MAX for a given identifier, and then get the rest of the data required, and failed to recheck the original problem to see if that was actually necessary in this case.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply