Solved

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

Posted on 2012-03-29
9
445 Views
Last Modified: 2012-03-29
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
Comment
Question by:MeowserM
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
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
 
LVL 6

Accepted Solution

by:
HomerTNachoCheese earned 500 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 

Author Closing Comment

by:MeowserM
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
ROW_NUMBER() should perform much better instead of using min subquery...
0
 
LVL 6

Expert Comment

by:HomerTNachoCheese
Comment Utility
HainKurt -

I have never used ROW_NUMBER.  Do you have any references to it that shows how it performs better?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 

Author Comment

by:MeowserM
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now