Solved

Highest Values in Query

Posted on 2012-03-31
6
292 Views
Last Modified: 2012-03-31
Hi All

I'm trying to create a query to get a list of purchase items that shows only the last time an item was purchased. The Table is a Purchase Item table where there are many duplicates.

I have a date field for each purchase (so the records are not duplicates really) I only want the ones with the latest date where there are duplicate discription, price and referance number.

How?
0
Comment
Question by:DatabaseDek
  • 3
  • 2
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37791362
Use a grouping query:

SELECT ItemPurchased, Max(PurchaseDate) AS MostRecent
FROM YourTable
GROUP BY  ItemPurchased
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37791365
select *
from [Purchase Item]
where [date] =(select max([date]) from [Purchase Item] as p where p.[referance number]=[Purchase Item].[referance number]

you can use an itemID or productid if there is one, in place of [reference number]
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37791367
Or if you want to include all those fields:


SELECT ItemPurchased, description, Price, RefNo, Max(PurchaseDate) AS MostRecent
FROM YourTable
GROUP BY  ItemPurchased, description, Price, RefNo
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Closing Comment

by:DatabaseDek
ID: 37791413
Just as a matter of interest is it possible to get the latest 2 records from the duplicates?.

Brilliantly simple. Thank you
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37791475
So the top two records for each 'group'?

That is definitely doable, but a little more complicated.

This page has a good explanation:
http://www.sql-ex.ru/help/select16.php

The first solution in that thread will work in Access SQL.

The second does not - I believe it is specific to SQL Server.
0
 

Author Comment

by:DatabaseDek
ID: 37792072
Thank you.That's interesting.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

792 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