Grouping Data and Selecting highest date

In SQL 2005 I have the following view:

SELECT TOP (100) PERCENT StockCode, Warehouse, QtyOnHand, QtyAllocated, QtyOnOrder, QtyOnBackOrder, DateLastSale, DateLastStockMove,
DateLastPurchase
FROM dbo.MBL_VW_AgedStock_Sales
ORDER BY StockCode

This basically shows a list of stock codes (there are multiple stock codes the same) and the last sold date. What i need to do is group the stock codes which are the same together, and show the latest date.

For example I could have the following:

STOCK CODE Last Date Sold

PC1113 11/01/2007

PC1104 15/03/2007

PC1113 15/02/2007

What I want to see is a list that shows PC1113 with its latest sold date, i.e.

STOCK CODE Last Date Sold

PC1113 15/02/2007

PC1104 15/03/2007

Any ideas?

Thanks

Kris

select StockCode, max(DateLastStockMovie) from your_table_or_view

group by stockcode

order by stockcode

|||

Something like this:

SELECT

'Stock Code' = StockCode,

'Last Sold Date' = max( DateLastSale )

FROM dbo.MBL_VW_AgedStock_Sales

GROUP BY StockCode

ORDER BY StockCode

GROUP BY highest score per user

Hi there!

I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.

The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.

Here's my base query:

SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC

...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:

SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID

How can I write combine the two in a nested subquery to display only the top score per user?further, here are the table schema:

USERS
- UserID (INT)
- Name (VARCHAR)

BALLOTS
- UserID (INT)
- SubmissionGUID (VARCHAR)
- TotalTally (INT) DEFAULT '0' -- this is incremented by varying values as a user makes correct selections
- WinningTeam (VARCHAR)

also, you can just assume that there are multiple submission, with each submission consisting of 63 records in the BALLOTS table, each with the same SubmissionGUID.

so, a user's total point would be the SUM'med value of all of their records grouped by a SubmissionGUID. Thus, a user "JOHN", could have the following:

USER SUBMISSIONGUID NAME TOTALTALLY
-- -- -- ----
1 kugiuvbiu JOHN 45
2 olhilugiu STEVE 32
3 oih98y897 MARK 31
1 89769gibi JOHN 29
1 0980jpo90 JOHN 13

I'd like just to select each unique USER's highest TOTALTALLY and display that, and forget about the others.

Clear as mud? :)