0
table is rpt

custID    dates         stores
111089    2015-09-28    103
111089    2015-06-19    119
111089    2015-10-11    106
555555    2015-05-02    103
555555    2015-08-21    125
555555    2015-09-20    125
123456    2015-01-01    119
123456    2015-05-13    116
123456    2015-09-15    120
123456    2015-08-29    115

result should be

custID    dates         store
111089    2015-06-19    119
555555    2015-05-02    103
123456    2015-01-01    119

the table is a very big table and I need all custID and store with the earliest date. like the result above.  
only one row per custID
Lemar Gray
  • 13
  • 1
  • 5
  • Have a look at this similar [question](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group). – Jan Trienes Oct 16 '15 at 18:29

2 Answers2

3

You can do this with a windowed function with a PARTITION on the CustID and ordering by dates:

;With Cte As
(
    Select  *, Row_Number() Over (Partition By CustID Order By Dates Asc) As Row_Number
    From    rpt
)
Select  custID, dates, stores
From    Cte
Where   Row_Number = 1
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • Wonderful. Amazing. Its works like a charm. Thanks you very very much. You saved me a lot of time. rpt is a select on the same table so I just place the select there and it just work. Simply Amazing. Thanks again..... – Lemar Gray Oct 16 '15 at 18:45
2
SELECT rpt.custid, rpt.date, rpt2.stores
FROM (select r.custid, min(r.DATE) as 'Date'
      from rpt r
      group by r.custid) rpt

left join (select r.custid, r.DATE, r.stores
           from rpt r) rpt2 on rpt2.custid = rpt.custid and rpt2.date = rpt.date
Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • 1
    This would produce the correct results but is overly complicated and the performance is going to suffer because you have to hit the same data twice with two queries like that. – Sean Lange Oct 16 '15 at 18:37
  • The data is hit twice by creating/populating a cte then querying it, correct? I'm curious about the performance differences... if you could elaborate that would be appreciated. – Aaron Dietz Oct 16 '15 at 18:52
  • 1
    Because the cte will not cause a second table scan where a second select statement will. I will throw together a sqlfiddle as an example. You can then try it out on your system and you will see what I mean. – Sean Lange Oct 16 '15 at 19:06
  • 1
    Here you go. http://sqlfiddle.com/#!6/fbcf8/1 I forgot you can view the execution plan on the site too. Notice that with two select statements it has to perform two table scans, that is because you two separate queries hitting the base table. – Sean Lange Oct 16 '15 at 19:09
  • Thanks Sean, greatly appreciate the knowledge. – Aaron Dietz Oct 16 '15 at 19:13