Filter a table and add order to result in SQL

Enrico Stefanel 0
Viewed 34

Let's imagine I have a Table as follows:

Area ID Value
A 1 0.5
A 2 1.2
B 3 1.1
B 4 0.8
C 5 1.0
B 6 0.6
A 7 1.5

What I want to achieve, is filtering the table based on Area value, order the values by IDs and return the Value field and relative record orders for that specific Area. So, for example, If I filter the table by Area = B, I want to obtain the Table

Area Order Value
B 1 1.1
B 2 0.8
B 3 0.6

Thanks to anyone who would like to help me!

sql-server-2008 sql-server sql
1 Answers

What you need is ROW_NUMBER() function.

SELECT Area
  , ROW_NUMBER() OVER (PARTITION BY Area ORDER BY ID) AS Order
  , Value 
FROM YourTable 
WHERE Area = 'B' -- You can try without where, you'll get order by each area
Nenad Zivkovic0
Related