We help IT Professionals succeed at work.

sql query with subquery

BTMExpert
BTMExpert used Ask the Experts™
on
i need a query that can get an order by total in random order.

Select top(10) *
From (select Top(100) percent * order by total) as something)
order by newid()

that's what i'm trying to use but it's not working

please help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You don't have a 'From tablename'   in your subquery

Author

Commented:
i want to grab from the table of results from the subquery
Try using Common Table Expressions

Example
----------------

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

 
Hamed NasrRetired IT Professional

Commented:
List five records, and list the top 2 required records.