-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
  • 21,987
  • 13
  • 41
  • 69
Denzyl
  • 145
  • 6
  • Please show us your attempt. And show us what you expected results it, assuming what you have posted is sample data. – Dale K Feb 14 '21 at 07:23
  • 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
  • 451,927
  • 24
  • 239
  • 318
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
  • 69,583
  • 5
  • 45
  • 72
0

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

raghavendra reddy
  • 32
  • 2