RE: Nested Query?

[allyne]

Hello,

I have a table that looks like this:

Table_Name: Pt_Meds

Name Date Meds
Test 11/20/2014 aspirin
Test 11/20/2014 Amoxicillin
Test 11/20/2014 Prozac
Test 11/25/2014 Naproxen
Test 11/25/2014 Ativan

The end result I want will look like this where the Rec_ID count starts over for each day. I just can't seem to get the Rec_ID to work.

Name Date Rec_ID Meds
Test 11/20/2014 1 aspirin
Test 11/20/2014 2 Amoxicillin
Test 11/20/2014 3 Prozac
Test 11/25/2014 1 Naproxen
Test 11/25/2014 2 Ativan

Thanks for all your help!

 

[gmmastros]

try this:

Declare @Temp Table(Name VarChar(100), Date DateTime, Meds VarChar(100))

insert into @Temp Values('Test', '11/20/2014', 'aspirin')
insert into @Temp Values('Test', '11/20/2014', 'Amoxicillin')
insert into @Temp Values('Test', '11/20/2014', 'Prozac')
insert into @Temp Values('Test', '11/25/2014', 'Naproxen')
insert into @Temp Values('Test', '11/25/2014', 'Ativan')

Select *,
       Row_Number() Over (Partition By Name, Date Order By Meds) As Rec_ID
From   @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom