Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return the last version of set of records Expand / Collapse
Author
Message
Posted Sunday, November 03, 2013 5:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:53 PM
Points: 176, Visits: 387
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.
Post #1510945
Posted Sunday, November 03, 2013 5:29 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:32 PM
Points: 571, Visits: 3,743
Try this:

SELECT DocumentNum, LineNum, CustomerNum, MAX(VersionNum) AS MaxVersion
FROM tempSalesOrder
GROUP BY DocumentNum, LineNum, CustomerNum;
Post #1510949
Posted Sunday, November 03, 2013 6:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1510954
Posted Saturday, November 09, 2013 1:44 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:53 PM
Points: 176, Visits: 387
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.
Post #1512926
Posted Sunday, November 10, 2013 5:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1512991
Posted Monday, November 11, 2013 9:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 845, Visits: 2,331
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.
Post #1513175
Posted Monday, November 11, 2013 5:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1513304
Posted Tuesday, November 12, 2013 10:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 845, Visits: 2,331
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.
Post #1513542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse