We help IT Professionals succeed at work.

MS SQL 2000 SELECT BOTTOM 12 RECORDS

iepaul
iepaul asked
on
Medium Priority
3,038 Views
Last Modified: 2012-05-06
I need to select the bottom 12 records in a table.  I cannot use order desc as I need the records in ascending order.  I have seen this example in experts exchange but I have been unable to get it to work for my select statment.

SELECT t2.*
FROM (SELECT TOP 10 t1.* FROM Good_T t1 ORDER BY t1.GoodID DESC)  t2
ORDER BY t2.GoodID

here is my original query -

SELECT MONTH(fldDateM), CONVERT(INT, SUM(fldDirectM)/SUM(fldTotalM) * 100)
FROM tblUtilizationMonth GROUP BY fldDateM ORDER BY fldDateM

and here is my attempt to merge the two -

SELECT t2.*
FROM (SELECT TOP 12 t1.MONTH(fldDateM), t1.CONVERT(INT, SUM(fldDirectM)/SUM(fldTotalM) * 100)
FROM tblUtilizationMonth GROUP BY t1.fldDateM ORDER BY t1.fldDateM DESC)  t2
ORDER BY t2.fldDateM ASC
Comment
Watch Question

Commented:
 0  vote down
      

If you know how many rows there will be in total you can use the ROW_NUMBER() function. Here's an examble from MSDN (http://msdn.microsoft.com/en-us/library/ms186734.aspx)



USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

Open in new window

try this

SELECT t2.*
FROM (SELECT TOP 12 datepart(month,fldDateM), CONVERT(INT, SUM(fldDirectM)/SUM(fldTotalM) * 100)
FROM tblUtilizationMonth GROUP BY t1.fldDateM ORDER BY t1.fldDateM DESC)  t2
ORDER BY t2.fldDateM ASC

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Hi,

Try

SELECT t2.*
FROM (SELECT TOP 12 MONTH(t1.fldDateM) AS fldDateM, CONVERT(INT, SUM(t1.fldDirectM)/SUM(t1.fldTotalM) * 100)  AS percent
FROM tblUtilizationMonth t1 GROUP BY t1.fldDateM ORDER BY t1.fldDateM DESC)  t2
ORDER BY t2.fldDateM ASC

/peter

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.