either

SELECT TOP 2 ORDER BY dischargedate DESC

or a CTE using

ROW_NUMBER() OVER(PARTITION BY id ORDER BY dischargedate DESC) AS row

and a query using WHERE row<=2

It shouldn't cause a performance issue since the XML structure is neither complicated nor large in size.

The best way to know is to test it. 😉

Depending on the Server version you're using you could either use OpenXML or XQuery.

You might want to have a look at Jacob Sebastians blog

If you can't find a solution please...

It looks like a FiFo stock problem as discussed in one of Phil Factor SQL Speed Phreak Competitions.

You might want to have a look at Daven Ballantynes answer. The main...

first of all: THANK YOU for providing ready to use sample data. EXCELLENT job!!

Regarding the data itself:

How do you assign a box to a PO? E.g. item01, box01 could be...

Another issue you need to consider:

How are you going to prevent a user adding a company "My company", if there is already a company named "My company Inc." together with...

Slightly different over here in Germany: if a holiday falls on a Sunday (as it happens with May 1st this year), we won't get a public holiday during the week...

Please read the first article referenced in my signature and post table def and sample data in a ready to use format together with your expected result set.

As an alternative...

removed. doesn't work under a different test condition...

You can't use a DROP TABLE statement using a variable unless you're using dynamic sql.

Are you familiar with dynamic SQL? What skill level do you have?

You could go through some of the questions asked (and answered) in the SS2K5 Performance forum (http://www.sqlservercentral.com/Forums/Forum360-1.aspx).

Or search for "performance tuning" on this site to find numerous questions. Try to...

Maybe Grants blog[/url] will help you to better understand what's going on.

Have a look at the UNPIVOT syntax.

If your original data include a column with a sequential number ,you could avoid the sort caused by ROW_NUMBER() and just use the very last part (the self-join). This should...

Vedran Kesegic (4/27/2011)

I would rather use a temp table than a table variable, because table variable...