How can you select the first 99.8% records from a table in TSQL-CodePudding

I am trying to convert this query into TSQL

MySQL Query:

SELECT count(item_id) as item_count, avg(sale_time) as average_duration 
FROM 
( 
  SELECT abc_products.*, @counter := @counter  1 counter 
  FROM (select @counter:=0) initvar, abc_products 
  INNER JOIN abc_details details on details.cb_id = abc_products.cb_id 
  WHERE 
  stamp >= '2021-12-01 00:00:00' 
  and 
  stamp < '2022-01-01 00:00:00'
  and 
  sale_time is not null 
  ORDER BY sale_time desc 
) X 
where counter <= (99.8/100 * @counter) 
ORDER BY sale_time desc

TSQL Query:

DECLARE @counter int;
-- SET @counter = 0;
SELECT count(item_id) as item_count, avg(sale_time) as average_duration 
FROM 
( 
  SELECT abc_products.*, @counter = @counter  1 AS counter 
  FROM (SET @counter = 0) initvar, abc_products 
  INNER JOIN abc_details details on details.item_id = abc_products.item_id 
  WHERE 
  stamp >= '2021-12-01 00:00:00'  
  and 
  stamp < '2022-01-01 00:00:00'
  and 
  sale_time is not null 
  ORDER BY sale_time desc 
) X 
where counter <= (99.8/100 * @counter) 
ORDER BY sale_time desc

Error: SQL Error [102] [S0001]: Incorrect syntax near '='. It's giving me error near @counter = @counter 1 AS counter How can I write this into TSQL

I have tried this but it's providing me no results or wrong results.

SELECT abc_products.*, ROW_NUMBER() OVER(ORDER BY sale_time desc) AS counter

Any help would be appreciated.

CodePudding user response：

Sql sever supports the feature directly

SELECT count(item_id) as item_count, avg(sale_time) as average_duration 
FROM 
( 
  SELECT TOP(98.8) PERCENT abc_products.*
  FROM  initvar, abc_products 
  INNER JOIN abc_details details on details.item_id = abc_products.item_id 
  WHERE 
  stamp >= '2021-12-01 00:00:00'  
  and 
  stamp < '2022-01-01 00:00:00'
  and 
  sale_time is not null 
  ORDER BY sale_time desc 
) X

Dropped ORDER BY sale_time desc as it makes no sense when sale_time is aggregated.

CodePudding user response：

Here is a method of doing the same thing in straight SQL. I have taken the top 20% but this is easily modified.

CREATE TABLE cricket(player int, score int);
INSERT INTO cricket VALUES( 1, 100 );
INSERT INTO cricket VALUES( 2, 160 );
INSERT INTO cricket VALUES( 3, 180 );
INSERT INTO cricket VALUES( 3, 120 );
INSERT INTO cricket VALUES( 4, 150 );
INSERT INTO cricket VALUES( 5, 250 );
INSERT INTO cricket VALUES( 7, 110 );
INSERT INTO cricket VALUES( 8, 200 );
INSERT INTO cricket VALUES( 9, 140 );
INSERT INTO cricket VALUES( 10, 190 );
INSERT INTO cricket VALUES( 11, 130 );
INSERT INTO cricket VALUES( 12, 170 );

SELECT player, score,RowNum, AllRows
FROM (
SELECT player, score,
        ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum,
        (SELECT COUNT(*) FROM cricket) As AllRows
        FROM cricket )c
WHERE RowNum < AllRows/5
ORDER BY score DESC;