sql to select orders with lowest count-CodePudding

 ----------- ----------- 
|id.        |count.     |
 ----------- ----------- 
|     192044|          1|
|     192045|          1|
|     192046|          1|
|     793820|          2|
|     285030|          2|
|     924949|          5|
 ----------- -----------

How would I write my SQL statement to select the customers who have the min count? Is this a ranking function?

SUDO of what I think it should be:

SELECT *
FROM (SELECT id, count(id) as count from base_table group by id)
GROUP BY id, count
HAVING order_count = (SELECT MIN(count) FROM (SELECT id, count(id) as count from base_table id))

CodePudding user response：

SELECT * FROM <DataBase_Table_name> ORDER BY count ASC;

CodePudding user response：

We can try to use dense_rank window function to make it, which might generate number from lowest count.

SELECT *
FROM (
 SELECT *,dense_rank() OVER(ORDER BY count) rn
 FROM T
) t1
WHERE rn = 1

from your comment it might be as blow query.

SELECT id,count
FROM (
 SELECT *,dense_rank() OVER(ORDER BY count) rn
 FROM (
    SELECT id, count(id) as count 
    FROM base_table 
    GROUP BY id 
 )
) t1
WHERE rn = 1

CodePudding user response：

You can do something like this:

Select id
From tbl t
Outer apply (select min(count) as mincount from tbl) s
Where count = mincount

Basically you use a subquery to get global minimum count, and then use OUTER APPLY to combine each row from your table with this result set.

CodePudding user response：

try this:

with base as(
  select 
     id
     , count
     , min(count) over() as min_value
  from table
  order by count desc
 ) select *
   from base
   where count = min_value