How to select first value - SQL Server

Question

I want to select the first date for each invoice as you can see below:

This is my data:

EC_Refpiece | EC_Date
FA0001      | 01/01/2019
FA0001      | 25/08/2018
FA0002      | 12/01/2019
FA0002      | 15/04/2017

And this is the result I want :

EC_Refpiece | EC_Date
FA0001      | 25/08/2018
FA0002      | 15/04/2017

Please show us your code and indicate what you don't understand. — HereGoes, Aug 09 '19 at 14:24
If all you want is the first date, use a simple `GROUP BY` and `MIN`. Are you looking for something different perhaps? — Panagiotis Kanavos, Aug 09 '19 at 14:29
@Rahul I googled it first but I didn't get the result I'm looking for! Anyway if you can't help at least don't be negative. Thank you. :) — hmzshl, Aug 09 '19 at 14:46
Okay so, this is what i wrote in google `select first date from a bunch of records sql server` and this is the FIRST link i got: https://stackoverflow.com/questions/5736820/sql-how-to-select-earliest-row. Not being negative but you are required to try to work on the solution yourself and then post a question. — Rahul, Aug 09 '19 at 14:51

score 1 · Accepted Answer · answered Aug 09 '19 at 14:25

1

I think this should do the job:

SELECT EC_Refpiece, MIN(EC_date)
FROM table
GROUP BY EC_Refpiece

answered Aug 09 '19 at 14:25

Rob Streeting

1,675
3
16
27

Thank you so much, MIN function works well. – hmzshl Aug 09 '19 at 14:36

score 0 · Answer 2 · answered Aug 09 '19 at 14:25

If you want to select the first, ie minimum date, all you need to do is a simple GROUP BY:

select EC_Refpiece, MIN(EC_DATE)
from ThatTable
group by EC_Refpiece

If you want to retrieve the first row though, you can use ROW_NUMBER for this :

   with invoices as 
   (
       select 
           EC_Refpiece,
           EC_Date,
           ....           
           ROW_NUMBER() OVER (partition by EC_Refpiece order by EC_Date) RN
       from ThatTable
   )
   select * from invoices
   where RN=1

ROW_NUMBER partitions the results according to the PARTITION BY clause and calculates a sequential row number for each partition, after sorting all results according to the ORDER BY clause

Thank you so much, MIN function works well. – hmzshl Aug 09 '19 at 14:36 — hmzshl, Aug 09 '19 at 14:36

How to select first value - SQL Server

2 Answers2