?
Solved

MS SQL 2000 SELECT BOTTOM 12 RECORDS

Posted on 2009-02-18
3
Medium Priority
?
2,916 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
0
Comment
Question by:iepaul
3 Comments
 
LVL 9

Expert Comment

by:TvMpt
ID: 23669162
 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

0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 23669167
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23669169
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

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question