Emulate group by, order by, limit from mysql to ms sql 2000

Question

I have a query in MySQL:

select slscod,slsname,brc
from td_casa
group by slscod
order by slsname
limit 0,100

This query retrieves the top 100 unique sales codes with slsname and brc, ordered by slsname ascending.

How to change this query in MSSQL 2000?

Have you tried running it on SQL Server? Does it return any error messages? — a'r, Oct 26 '11 at 10:02
@a'r: this isn't valid SQL Server syntax which is quite obvious. Which is why the question has been asked... — gbn, Oct 26 '11 at 10:29

Sparky · Answer 1 · 2011-10-26T10:25:33.280

3

The basic syntax is:

select top 100 slscod,slsname,brc 
from td_casa 
group by slscod 
order by slsname

edited Oct 26 '11 at 10:25

answered Oct 26 '11 at 10:03

Sparky

14,318
1
27
43

does not work, I get an error : [Err] 42000 - [SQL Server]Column 'td_casa.slsname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Irfani Firdausy Oct 26 '11 at 10:47

Salman A · Answer 2 · 2011-10-26T11:25:41.543

2

SELECT TOP 100 slscod, slsname, brc
FROM td_casa
GROUP BY slscod, slsname, brc
ORDER BY slsname

Note: regarding your comment about Column 'xyz' is invalid in the select ... error I noticed that you were selecting 3 columns but specified 1 column in the GROUP BY clause. This is a MySQL specific behavior as described here:

MySQL extends the use of GROUP BY to permit selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 11.15, "Functions and Modifiers for Use with GROUP BY Clauses".

edited Oct 26 '11 at 11:25

answered Oct 26 '11 at 10:56

Salman A

229,425
77
398
489

I had a problem again if taking the limit, for example in mysql I want to take the start record 30 until 100. This was in mysql query : select slscod,slsname,brc from td_casa group by slscod order by slsname limit 30,100. How to change this query in MSSQL 2000? – Irfani Firdausy Oct 26 '11 at 11:13
@IrfaniFirdausy: See this: http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server – ypercubeᵀᴹ Oct 26 '11 at 11:16
1

I am afraid with MSSQL2000 your hands are tied. But this is worth looking into: http://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword – Salman A Oct 26 '11 at 11:18
Oh, yes, SQL-Server-2000 has limited options for the OFFSET version. See also this article: http://www.4guysfromrolla.com/webtech/041206-1.shtml – ypercubeᵀᴹ Oct 26 '11 at 11:23
And the answer by @leoinfo: http://stackoverflow.com/questions/187998/row-offset-in-sql-server – ypercubeᵀᴹ Oct 26 '11 at 11:23
And a rather old article with some performance analysis on various methods (not all applicable to SQL-Server 2000): http://www.codeproject.com/KB/aspnet/PagingLarge.aspx – ypercubeᵀᴹ Oct 26 '11 at 11:30
Instead of using `MIN`, just extend the `GROUP BY` clause to include all "selected" columns. Both methods will return same results (assuming that sslcod is a primary/unique key). – Salman A Oct 26 '11 at 12:41
finally, i found this query. I'll answer it tomorrow. because Oops! Your answer couldn't be submitted because: Users with less than 100 reputation can't answer their own question for 8 hours after asking. :) – Irfani Firdausy Oct 26 '11 at 13:04

score 0 · Answer 3 · edited Dec 12 '11 at 23:39

because in MSSQL 2000 does not support the ROW_NUMBER() and LIMIT ... OFFSET .. finally, i found this query :

SELECT slscod, MIN(slsname) slsname, MIN(brc) brc FROM (
    SELECT top 30 slscod, MIN(slsname) slsname, MIN(brc) brc FROM
    (
        SELECT TOP (1*30) slscod, MIN(slsname) slsname, MIN(brc) brc
        FROM td_casa group by slscod
        ORDER BY slsname ASC
    ) AS t1 group by slscod
     ORDER BY slsname DESC ) AS t2 group by slscod ORDER BY MIN(slsname) ASC

This is the same if in mysql :

select slscod,slsname,brc from td_casa group by slscod order by slsname limit 0,30

if you change TOP (2*30) this is the same limit 30,30. TOP (3*30) this is the same limit 60,30 and so on.

desperate need of effort. thanks all.let's cheers

Emulate group by, order by, limit from mysql to ms sql 2000

3 Answers3