-3

I'm currently trying to find the TOP(1) Number_of_Orders based on the different state. How would one go about doing that?

SQL Table

Dale K
  • 25,246
  • 15
  • 42
  • 71
Denzyl
  • 166
  • 1
  • 11
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – GSerg Feb 14 '21 at 10:00

3 Answers3

2

A version I like, which avoids an explicit subquery:

SELECT TOP 1 WITH TIES *
FROM yourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY state ORDER BY Number_of_Orders DESC);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

Like this

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY state ORDER BY number_of_orders DESC) r
  FROM t
)
SELECT * FROM cte WHERE r = 1

Row number function establishes an incrementing counter starting at 1 that increases as number of orders decreases, and it restarts at 1 when state changes

If you want ties, use DENSE_RANK instead of row_number

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
0

select state,max(Number_of_Orders) as [Top 1 Order] from [yourtable] group by state

raghavendra reddy
  • 32
  • 2