Top Value Per Unique ID?

[NCRStinks]

Hi There

Trying to figure out how to do this was hoping for some advice.

I have two tables. The first holds the store details with store id, store name etc. The second table holds the Store Status eg new, lfl, refit, resite and so on.

I would like to run a query which finds the "top" value per store id.

Any help greatly appreciated!


Many Thanks


Dan

[NeoPa]

What would "top" mean in this situation. I see no numeric or obviously relative items in your explanation.

[NCRStinks]

What would "top" mean in this situation. I see no numeric or obviously relative items in your explanation.

Hi There

Sorry wasn't completely clear with my post. each status will have a status date. I would like the "top" value per status date.

Many Thanks

Dan

[NeoPa]

I shouldn't have to ask you to post the information required to even understand the question. Having gone to that trouble, I don't expect to have to point out again that the information provided is inadequate. I won't continue as I'm in danger of saying something I may later regret.

[NCRStinks]

I shouldn't have to ask you to post the information required to even understand the question. Having gone to that trouble, I don't expect to have to point out again that the information provided is inadequate. I won't continue as I'm in danger of saying something I may later regret.

well thats a bit rude. so much for these forums being helpful.

its quite obvious that i was refering to the most recent .

[Kaallis]

I am not an expert, but we would still need more informations to help you.
I am not a flammer and I won't flame you since I have no reasons to do so anyway.

Regards

[jaxjagfan]

Still not sure what you are looking for however:

Put together your query with the associated linking tables till you have columns you want.
Test it to make sure the data will be displayed.
Switch to SQL view and add TOP 1 to the select statement.

SELECT TOP 1 StoreID, StoreName, Status, StatusDate
From "whatever your table names and joins are"
Order by StatusDate

The TOP 1 predicate will only select the very first store selected in the query. If you have 25 stores then put TOP25. I don't like doing this due to number of stores varying over time.

Use same query and take out the TOP 1. Try using a Group By and select Max(StatusDate) and First(StatusID). This would return all rows for that row's "most recent status date".

It depends on what you want as results - your post is still vague.

[NeoPa]

well thats a bit rude. so much for these forums being helpful.

its quite obvious that i was refering to the most recent .

You seem to have a difficulty understanding what is, and what is not good manners.
Bad manners is asking for help without bothering even to spend any effort on formulating a clear question (in fact it was not just unclear. It was fundamentally incorrect).
Compounding that by simply repeating what you'd already said when asked for clarification - and implying that I'm stupid for not understanding your sloppy post, is also bad manners (obviously).

Part of my job is to guide posters when they fail to observe the rules of this site (Posting Guidelines).
I believe I managed to do this simply stating facts that, indeed, needed to be stated. If you see that as rude then we clearly have very different ideas as to what constitutes good manners.

If you're unhappy with my response you have every right to refer this to another administrator to consider.

ADMIN.

[Rabbit]

Perhaps I can clear up what's confusing about your question.

I would like to run a query which finds the "top" value per store id.

We don't know what value you're talking about here.

each status will have a status date. I would like the "top" value per status date

We were talking about "per store id" and all of a sudden we're talking about "per status date." Also, again you use the word value without qualifying what that is. By inference I assume you mean status. So it sounds as if you want the "top" status per status date. Even if this were correct, we don't know what your status variable holds. So by top are we talking about sorted alphabetically? Sorted numerically? Descending? Ascending?

its quite obvious that i was refering to the most recent .

Most recent? Are you talking about most recent status date as top? That's not what you said earlier, or even in your first post. From the prior post it would seem you want top status, but in this one it sounds like you want most recent status.

In each post your definition of "top" and "grouping variable" changes so you should be able to see why we're confused.

And that's not to mention that I think this older post is the same as this post. And in it you confused me and never responded to my last post.