Programmatically generating top n queries for Microsoft SQL Server from a general query?

I think the issue is he knows the count AFTER he knows the subquery. – JNK Jan 4 at 1:18 That would work in this case, but I'd like to be able to pass any query (as a string; updating my question to note this) to the function and get a query that yields the expected result. Simply replacing the first SELECT doesn't yield the expected results for e.g. Select x from test1 union select x from test2 (you get all the rows).

– Samuel Edwin Ward Jan 4 at 14:36 It helps me to remember that the thing returned by a subquery is always a set (even if you SELECT TOP.. in the subquery) and sets have no order. If you have to use a subquery then JNK's approach is better. – Andrew Jan 4 at 17:55.

You can make proper use of CTE (Common Table Expressions) and Ranking Functions here. ; WITH CTE_RowNumbers AS ( SELECT *, RANK() OVER (ORDER BY X) AS ROWNUM FROM Users ) SELECT * FROM CTE_RowNumbers WHERE ROWNUM Please note: The ROWNUM in this code snippet is just an alias, not a function in MSSQL and the real power sits in the use of the common table expressions and ranking functions used. UPDATE: Masked CTE - replace the VAR-Name with your variables ; WITH CTE_RowNumbers AS ( SELECT VAR-ColumnList, RANK() OVER (ORDER BY VAR-OrderByList) AS ROWNUM FROM VAR-Table ) SELECT VAR-ColumnList FROM CTE_RowNumbers WHERE ROWNUM.

– Samuel Edwin Ward 19 hours ago @SamuelEdwinWard I can try and point you in a direction... Look at your input query and the query I provided. Provided your input query always has the same basic structure (needs the ORDER BY) you can extract a number of variables (about 3). These will be the column list (* in this case), the table name, and the order by column list (X).

Now you can create the basic structure of the CTE as a string and replace markers with the variables. I will update the answer and provide a mask for the CTE in a minute or so... – Charl Lamprecht 2 hours ago.

The easiest and most logical solution would be to put your ORDER BY outside the subselect: SELECT TOP 10 * FROM (SELECT * FROM TEST) as sub ORDER BY X SQL Server expects the TOP and ORDER to be in the same scope. Even if you ORDER BY on the inner query, if you did a TOP on the outside it wouldn't be guaranteed to be in the right order.

You need an alias on the derived table. – Mikael Eriksson Jan 3 at 22:04 @MikaelEriksson - good point – JNK Jan 4 at 1:13.

Use SET ROWCOUNT. This makes the job really simple. SET ROWCOUNT 10 SELECT * FROM TEST ORDER BY X SET ROWCOUNT 0.

I tend to agree with this one. It's very easy to prefix / suffix ANY string with the ROWCOUNT limitation/reset and it works for pretty much anything you put it around. That said, if the queries are going to be multi-statement you might have strange effects as ALL statements will get limited to n rows.

– deroby yesterday This is looking pretty good and seemed to work in the situation that led to the question. But I have a funny feeling that there's some downside or gotcha that could catch me by surprise later. – Samuel Edwin Ward yesterday.

Subqueries are sets, and are therefore unordered, so you can't use them with ORDER BY unless you also use TOP. If you want to limit the number of rows returned, and don't care which rows they are, you can just edit out any inner ORDER BY, if there was one, and then provide an alias for the subquery. Original query: SELECT * FROM TEST ORDER BY X Edited and wrapped to return 10 rows: SELECT TOP 10 * FROM (SELECT * FROM TEST) AS EXPR Or you can apply a TOP clause to the subquery: SELECT TOP 10 * FROM (SELECT TOP 10 * FROM TEST ORDER BY X) AS EXPR Unfortunately, this is not guaranteed to return the same results as: SELECT TOP 10 * FROM TEST ORDER BY X For that, you would need to move the ORDER BY outside of the subquery: SELECT TOP 10 * FROM (SELECT * FROM TEST) AS EXPR ORDER BY X You can also parameterize the number of rows to return: SELECT TOP (@nrows) * FROM (SELECT * FROM TEST) AS EXPR With SQL Server 2012, you can almost achieve what you're really after, except in this case ORDER BY is required: SELECT * FROM TEST ORDER BY X OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY.

You could try a temporary table: select * into #temp from test order by x select top 10 * from #temp where ...

– Samuel Edwin Ward 17 hours ago Even though I've never seen it not preserve the order, I must honestly say that I don't really know whether there's a definition for how results are ordered selecting from a table without an index. – Thorsten Dittmar 2 hours ago By the way: The same would be true if SELECT * FROM (SELECT ... ORDER BY ...) worked, as you'd not be specifying an explicit order in your surrounding select. – Thorsten Dittmar 2 hours ago.

I think that this is what you're looking for (I'm assuming SQL Server 2005 and above): SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY x) rownum FROM test) tmp WHERE rownum BETWEEN 1 AND 10 Where 'x' is the column to order by and 'test' the table to select from. Also, a 'count' column is often also useful to return, especially if you do paging: SELECT * FROM (SELECT *,COUNT(*) OVER() cnt,ROW_NUMBER() OVER (ORDER BY x) rownum FROM test) tmp WHERE rownum BETWEEN 1 AND 10.

Do one thing which is quite simple and straight forward:-->> Remove the order by clause and use the keyword 'AS' after the inner query. Eg: SELECT TOP 10 * FROM (SELECT * FROM TEST) AS X.

1 I guess he also cares about the wanted result, not just have something that compiles – deroby yesterday Removing the 'order by' clause would remove the ordering, which I want to preserve. And aside from that, I don't believe removing an ORDER BY clause from a SQL string (and getting the correct result) without parsing the SQL is trivial (although I'd be pleased to be shown wrong on this). – Samuel Edwin Ward yesterday.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.