CODEWITHSUNDEEP
sql-servert-sql
Andryx93
Andryx93

Reputation: 81

T-SQL Select one row for multiple groups from one table

I have the following table:
NUMBER | DATE | VALUE_1 | VALUE_2
145789 | 2016-10-01 | A | Carrot
145789 | 2016-10-03 | B | Apple
145789 | 2016-10-14 | C | Banana
748596 | 2016-10-07 | Mango | Watermelon
748596 | 2016-10-19 | Pear | Strawberry
748596 | 2016-10-30 | Orange | Avocado

I want to select the first record for each number (the record with the minimum date).
How can I have a result like this?
NUMBER | DATE | VALUE_A | VALUE_B
145789 | 2016-10-01 | A | Carrot
748596 | 2016-10-07 | Mango | Watermelon

Upvotes: 3

Views: 193

Answers (1)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Very simple. You need to use row_number() for this, like below. Below we have generated unique numbers(Using Row_number) for each Number group rows based on date. On top of it we have selected only minimum date record (By using where clause ). For More about row_number click here.

 SELECT [NUMBER], [DATE], [VALUE_1], [VALUE_2]
    FROM 
    (

    SELECT *,ROW_NUMBER() OVER(PARTITION BY NUMBER ORDER BY DATE ASC) RNO
    FROM TABLE1)A
    WHERE RNO=1

Upvotes: 4

Related Questions