Getting the min record from a subset of data - query help

I am trying to write a sql query that will allow me to get the record with the min date from another query I have written.

So I wrote a query that looks like this:

Select ID, EventDate, User, Datestamp
From Table
Where EventDate is not null and id='123'

The results are:
ID      EventDate   USER   DateStamp
123    11/25/2010  4567  11/30/2010
123    11/25/2010  4567  11/30/2010
123    11/25/2010  8910   12/3/2010
123    11/25/2010  8910  12/3/2010
123    11/25/2010   1112   12/14/2010

That works great, but I want to get the record where the DateStamp is MIN.  In my example the result I would want is:
ID      EventDate   USER   DateStamp
123    11/25/2010  4567  11/30/2010

So I tried the query like this:
Select ID, EventDate, User, min(Datestamp) as minDatestamp
From
(Select ID, EventDate, User, Datestamp
From Table
Where EventDate is not null and id='123'
) a
Group by ID

But I get an error message that says "a.User is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

What am I doing wrong??


Thank you!
MeowserMAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HomerTNachoCheeseConnect With a Mentor Commented:
Select Table.ID, Table.EventDate, Table.User, Table.Datestamp
From Table INNER JOIN (SELECT Min(Table.Datestamp) as MinDateStamp FROM Table Where EventDate is not null and id='123') as SubQuery1 ON Table.DateStamp = SubQuery1.MinDateStamp

From there if you want to group you can add that part in.
0
 
AshokCommented:
Try

Select ID, EventDate, User, min(Datestamp) as minDatestamp
From
(Select Distinct ID, EventDate, User, Datestamp
From Table
Where EventDate is not null and id='123'
) a
Group by ID, EventDate, User

HTH
Ashok
0
 
HainKurtSr. System AnalystCommented:
try this

select * from (
Select ID, EventDate, User, Datestamp, ROW_NUMBER() over (order by DateStamp) rn
From Table
Where EventDate is not null and id='123'
) x where rn=1

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
MeowserMAuthor Commented:
Thank you.  I had a little trouble at first until I realized I had to add the group by into the subquery.

Thank you again!
0
 
HainKurtSr. System AnalystCommented:
ROW_NUMBER() should perform much better instead of using min subquery...
0
 
HomerTNachoCheeseCommented:
HainKurt -

I have never used ROW_NUMBER.  Do you have any references to it that shows how it performs better?
0
 
HainKurtSr. System AnalystCommented:
I am using such queries all the time and sometimes there is no other way of getting the same result...

you may check this nice article: http://www.sql-server-performance.com/2006/ranking-functions-performance/
0
 
HainKurtSr. System AnalystCommented:
also with accepted solution, if there are multiple records with same datestamp, you will end up with multiple records... with row_number() you can also other columns to select the right row...

ROW_NUMBER() over (order by DateStamp, EventDate desc) rn
0
 
MeowserMAuthor Commented:
HainKurt -

You have a valid point.  I did end up changing the query to look for the min of the indexed row id.

thank you,
MeowserM
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.