Top Value Per Unique ID? 3

[danneedham]

Hi All

I have been pondering over this one for a while... I'm quite a basic user trying to improve the systems at work.

I am trying to update the store database we currently have.

It consists of two tables. Store, which holds store id, name etc. And store status, which holds status date and status name.

I was wondering whether it was possible to have a query where the top value (most recent) is shown for each unique id (store)

Any help would be greatly appreciated.

Thanks


Dan

 

[gmmastros]

I suspect this is for academic purposes. As such, I will guide you, but not give you a solution.

In a case like this... forget about TOP. Instead, focus your attention on the Max aggregate and the group by clause.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[danneedham]

Hi There

I have just tried the MAX function by using the following statement:

SELECT StoreID, MAX(StatusDate) AS StatusDate
FROM Status
GROUP BY StoreID

-- How do i get the other information from the table into the query?? I would like to see the status label?

Many Thanks


Dan

 

[AlexCuse]

A little light reading:

faq183-4785

Hope it helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[jbenson001]

Select s.col1, s.col2, .... rest of column list...
From
Status s
Inner Join
(
SELECT StoreID, MAX(StatusDate) AS StatusDate
FROM Status
GROUP BY StoreID
) B ON
s.StoreID = b.StoreID

 

[gmmastros]

What jbenson001 is showing you is a [google]derived table[/google] method for building queries. This is a very powerful method to use, so it's important that you understand it completely. If you have any questions regarding derived tables, just let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Thanks to you George, I understand and use them a lot more..

 

[danneedham]

Thanks all.

Think I'm going to have some reading up to do to completely understand this.

Thanks for all your help!


Dan