Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-29
9
Medium Priority
?
457 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
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 61

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
Industry Leaders: 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!

 

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 61

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 61

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 61

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 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