Replacement for row_number() in clickhouse

Question

Row_number () is not supported by clickhouse database, looking for a alternate function.

SELECT company_name AS company,
       DOMAIN,
       city_name AS city,
       state_province_code AS state,
       country_code AS country,
       location_revenue AS revenueRange,
       location_TI_industry AS industry,
       location_employeecount_range AS employeeSize,
       topic,
       location_duns AS duns,
       rank AS intensityRank,
       dnb_status_code AS locationStatus,
       rank_delta AS intensityRankDelta,
       company_id,
       ROW_NUMBER() OVER (PARTITION BY DOMAIN) AS rowNumberFROM company_intent c
 WHERE c.rank > 0
   AND c.rank <= 10
   AND c.signal_count > 0
   AND c.topic IN ('Cloud Computing')
   AND c.country_code = 'US'
   AND c.rank IN (7, 8, 9, 10)
 GROUP BY c.location_duns,
          company_name,
          DOMAIN,
          city_name,
          state_province_code,
          country_code,
          location_revenue,
          location_TI_industry,
          location_employeecount_range,
          topic,
          rank,
          dnb_status_code,
          rank_delta,
          company_id
 ORDER BY intensityRank DESC
 LIMIT 15 SELECT COUNT (DISTINCT c.company_id) AS COUNT
  FROM company_intent c
 WHERE c.rank > 0
   AND c.rank <= 10
   AND c.signal_count > 0
   AND c.topic IN ('Cloud Computing')
   AND c.country_code = 'US'
   AND c.rank IN (7, 8, 9, 10)

When executed the above query got the below error.

Expected one of: SETTINGS, FORMAT, WITH, HAVING, LIMIT, FROM, PREWHERE, token, UNION ALL, Comma, WHERE, ORDER BY, INTO OUTFILE, GROUP BY

any suggestions is appreciated

Answer 1

Solution #1

SELECT
    *,
    rowNumberInAllBlocks()
FROM
    (
        -- YOUR SELECT HERE
    )

https://clickhouse.com/docs/en/sql-reference/functions/other-functions/#rownumberinallblocks says:

rowNumberInAllBlocks() Returns the ordinal number of the row in the data block. This function only considers the affected data blocks.

Solution #2

SELECT
    row_number() OVER (),
    ...
FROM
    ...

https://clickhouse.com/docs/en/sql-reference/window-functions/

In my tests, both solutions show identical results. However, you need to remember that at the beginning of 2022, window functions work in single-threaded mode.