Link to home
Create AccountLog in
Avatar of Roberto Madro R.
Roberto Madro R.Flag for United States of America

asked on

Select the 2 most recent visit dates

I'm grouping on customerID in a query and want to pickup the last 2 visitdate, what's the best approach.

Thx
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Roberto Madro R.

ASKER

slightwv saves the day again. You helped me on a similar question couple of years back and I was recalling how to write it,  but bit in a hurry and had to pose the question again.
Many Thanks
I'm not sure if a follow-up question will cause the case to be reopen, but here's the question;

The solution provided gives me the following;

CutomerID, VisitDate
12345           20170101
12345           20170315

Is there a way to flatten the record, for example:

CustomerID, VisitDate1,  VisitDate2
12345            20170101    20170315

Appreciate any suggestion.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Again, untested but should go something like this:
select customerid, 
max(case when rn=1 then visitdate end) visitdate1,
max(case when rn=2 then visitdate end) visitdate2
 from (
select customerid, visitdate, row_number() over(partition by customerid order by visitdate desc) rn from sometable
)
where rn <=2
group by customerid
/

Open in new window

Worked like a charm.
In good hands with slightwv.
Many thanks