USE [new-satro];
GO
SELECT * FROM (
SELECT TOP 10 * FROM (
SELECT TOP 120 T.[id_t]
,T.[parent_id_t]
,T.[title_t]
,T.[min_op_t]
,T.[brend]
,T.[cena]
,T.[nali4ie]
,T.[artik]
,T.[cena_opt]
,T.[kolvo]
,T.[kolvo_yd_sklad]
,T.[full_op_t]
,T.[mgc]
,TV.VSEGO
FROM [tovari] T
LEFT JOIN ( SELECT COUNT([ID]) AS VSEGO, [TOVAR] FROM [tovari_view] GROUP BY [TOVAR] ) as TV on TV.[TOVAR]=T.[artik]
ORDER BY T.[id_t] ASC
) AS inner_tbl ORDER BY [id_t] DESC
) AS outer_tbl ORDER BY [id_t] ASC
GO
Или сортировка в хранимой процедуре
CREATE PROCEDURE Count_Tovar @brend AS VARCHAR(50), @cena AS INT, @otstup AS INT
AS
SELECT * FROM (
SELECT TOP 10 * FROM (
SELECT TOP (@otstup) T.[id_t]
,T.[parent_id_t]
,T.[title_t]
,T.[min_op_t]
,T.[brend]
,T.[cena]
,T.[nali4ie]
,T.[artik]
,T.[cena_opt]
,T.[kolvo]
,T.[kolvo_yd_sklad]
,T.[full_op_t]
,T.[mgc]
,TV.VSEGO
,PH.[name]
FROM [tovari] T
LEFT JOIN ( SELECT COUNT([ID]) AS VSEGO,[TOVAR] FROM [tovari_view] GROUP BY [TOVAR] ) as TV on TV.[TOVAR]=T.[artik]
CROSS APPLY ( SELECT TOP 1 [name],[artik_poz] FROM [photogall] WHERE [artik_poz]=T.[artik] ORDER BY [ID] DESC ) as PH
WHERE T.[brend] LIKE @brend AND T.[cena] >=@cena
ORDER BY TV.VSEGO DESC
) AS inner_tbl ORDER BY VSEGO ASC
) AS outer_tbl ORDER BY VSEGO DESC
GO