Contact Us

Use the form on the right to contact us.

You can edit the text in this area, and change where the contact form on the right submits to, by entering edit mode using the modes on the bottom right. 


Oak Ridge, TN, 37830
United States

Sql-Sangria

Re-Order on Order Field

Wade Cantley

The Orange Re-Order

  • 1 c. tequila
  • 1 c. freshly squeezed orange juice
  • ½ c. Licor 43
  • ¼ c. freshly squeezed lime juice
  • splash of pure vanilla extract
  • simple syrup or agave nectar, optional (to taste)

We will either put it on the end (append) which is indicated by an order of 0, or we will put it into an existing order, which will require incrementing everything after the given number.  And then reordering to get rid of holes.

SET @Order = 3

	-- If order number = 0, Generate actual order number to put it at the bottom
  IF @Order = 0
    BEGIN
      SELECT TOP 1 @Order = ATMPTC.ContentOrder + 1
      FROM cms.AscTblMountPointTblContent ATMPTC
      WHERE ATMPTC.MountPointId = @MountPointId
        -- some unique id that groups together items for ordering
      ORDER BY ATMPTC.ContentOrder DESC

      -- It is possible that this is the first record,
      -- in which case, the return would be 0.
      -- If thats the case, then set the number to 1

      IF @Order = 0
      BEGIN
        SET @order = 1
      END
  END


	-- If order number is not 0 make room for new ordernumber by incrementing everything after the order number
  ELSE
    BEGIN
      UPDATE cms.AscTblMountPointTblContent
      SET ContentOrder = ContentOrder + 1
      WHERE MountPointId = @MountPointId
      -- some unique id that groups together items for ordering
      AND contentOrder >= @order
    END

  -- Re-Order - Editting a ContentOrder number can cause a number gap so this cleans up a number gap.
SELECT ROW_NUMBER() OVER (order BY ATMPTC.ContentOrder ASC) AS NewRowId
, ATMPTC.ContentOrder
, ATMPTC.ContentId
FROM  CMS.AscTblMountPointTblContent ATMPTC
WHERE ATMPTC.MountPointId = @mountPointId
ORDER BY ATMPTC.ContentOrder ASC