• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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!
0
MeowserM
Asked:
MeowserM
  • 4
  • 2
  • 2
  • +1
1 Solution
 
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
 
HomerTNachoCheeseCommented:
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
 
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now