Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-29
9
Medium Priority
?
456 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37783196
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 2000 total points
ID: 37783234
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 59

Expert Comment

by:HainKurt
ID: 37783244
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Closing Comment

by:MeowserM
ID: 37783357
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
 
LVL 59

Expert Comment

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

Expert Comment

by:HomerTNachoCheese
ID: 37783410
HainKurt -

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

Expert Comment

by:HainKurt
ID: 37783743
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 59

Expert Comment

by:HainKurt
ID: 37783783
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
ID: 37784592
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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