Return Top Records for particular ID

J

jason

This query only returns the LAST record in the table. I want it to return
ALL records from the table )(which contain duplicate ListingsIDs) based on
the last ListingID added for that group of records.

Thus instead of just returning:
ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

* It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

Is this possible: Here is how my query currently looks (below). Could I just
add a * to the end of TOP? But, alas that does not work:

PARAMETERS LID Long;
SELECT TOP 1 tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, tblListingsPriceChanges.ChangeDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR (((tblListingsPriceChanges.ListingsID)=[LID]))
ORDER BY tblListingsPriceChanges.ChangeDate DESC;
 
B

Brian Camire

You might try a query whose SQL looks something like this:

SELECT
tblListingsPriceChanges.*
FROM
tblListingsPriceChanges
WHERE
tblListingsPriceChanges.ChangeDate =
(SELECT
Max(Self.ChangeDate)
FROM
tblListingsPriceChanges AS Self
WHERE
Self.ListingID = tblListingsPriceChanges.ListingID)
 
J

jason

Brian - that works like a dream! But can you help me integrate my parameter
LID. It is not immediately obvious how I do this without breaking the query:

I tried this but it is throwing up errors. I am basically allowing the query
to accept NULL (all records) or a specific ListingsID for the Parameter
LID....

PARAMETERS LID long;
SELECT tblListingsPriceChanges.*
FROM tblListingsPriceChanges
WHERE tblListingsPriceChanges.ChangeDate =
(SELECT
Max(Self.ChangeDate)
FROM
tblListingsPriceChanges AS Self
WHERE
Self.ListingsID = tblListingsPriceChanges.ListingsID) or( [LID] Is Null or
tblListingsPriceChanges.ListingsID=[LID) ;

Cheers
Jason

Brian Camire said:
You might try a query whose SQL looks something like this:

SELECT
tblListingsPriceChanges.*
FROM
tblListingsPriceChanges
WHERE
tblListingsPriceChanges.ChangeDate =
(SELECT
Max(Self.ChangeDate)
FROM
tblListingsPriceChanges AS Self
WHERE
Self.ListingID = tblListingsPriceChanges.ListingID)


jason said:
This query only returns the LAST record in the table. I want it to return
ALL records from the table )(which contain duplicate ListingsIDs) based on
the last ListingID added for that group of records.

Thus instead of just returning:
ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

* It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

Is this possible: Here is how my query currently looks (below). Could I just
add a * to the end of TOP? But, alas that does not work:

PARAMETERS LID Long;
SELECT TOP 1 tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, tblListingsPriceChanges.ChangeDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR (((tblListingsPriceChanges.ListingsID)=[LID]))
ORDER BY tblListingsPriceChanges.ChangeDate DESC;
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Jason:

So, I guess you're making some progress.

Your query has the user entering a value for ListingsID, but the
sample result shows multiple ListingsID values. You obviously cannot
get multiple ListingsID rows when the user picks a single LID value.
So, you must be looking at the case where the user doesn't enter an
LID value.

The query for this would be:

PARAMETERS LID Long;
SELECT ListingsID, NewPrice, ChangeDate
FROM tblListingsPriceChanges LPC
WHERE ([LID] IS NULL OR ListingsID = [LID])
AND ChangeDate = (SELECT MAX(ChangeDate
FROM ListingsPriceChanges LPC1
WHERE LPC1.ListingsID = LPC.ListingsID)
ORDER BY ChangeDate DESC;

However, if there are two rows for a single ListingsID value on the
same most-recent-date then it cannot choose between them, and you're
going to get both. You could choose to break this tie by showing the
larger or smaller NewPrice value (for example) but this too could be
duplicated, still leaving you with two rows for the same ListingID.
Or, perhaps, showing two rows for the same ListingsID value is not a
problem in this case.

This query only returns the LAST record in the table. I want it to return
ALL records from the table )(which contain duplicate ListingsIDs) based on
the last ListingID added for that group of records.

Thus instead of just returning:
ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

* It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

Is this possible: Here is how my query currently looks (below). Could I just
add a * to the end of TOP? But, alas that does not work:

PARAMETERS LID Long;
SELECT TOP 1 tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, tblListingsPriceChanges.ChangeDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR (((tblListingsPriceChanges.ListingsID)=[LID]))
ORDER BY tblListingsPriceChanges.ChangeDate DESC;
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
J

jason

:) Progress - baby steps!

Tom, your query is only returning ONE record when it should be returning 3
records. Thus, if the PARAMETER is NULL it should return all valid UNIQUE
LISTINGID records selected by Max(). Any ideas?

But this is what I get:

ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From:

tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

But this is what I want:
It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

This is how I cleaned up the your query:

PARAMETERS LID Long;
SELECT ListingsID, NewPrice, ChangeDate

FROM tblListingsPriceChanges

WHERE ([LID] IS NULL OR ListingsID = [LID])

AND ChangeDate =

(SELECT MAX(ChangeDate) FROM tblListingsPriceChanges WHERE
tblListingsPriceChanges.ListingsID = [tblListingsPriceChanges.ListingsID])

ORDER BY ChangeDate DESC

Tom Ellison said:
Dear Jason:

So, I guess you're making some progress.

Your query has the user entering a value for ListingsID, but the
sample result shows multiple ListingsID values. You obviously cannot
get multiple ListingsID rows when the user picks a single LID value.
So, you must be looking at the case where the user doesn't enter an
LID value.

The query for this would be:

PARAMETERS LID Long;
SELECT ListingsID, NewPrice, ChangeDate
FROM tblListingsPriceChanges LPC
WHERE ([LID] IS NULL OR ListingsID = [LID])
AND ChangeDate = (SELECT MAX(ChangeDate
FROM ListingsPriceChanges LPC1
WHERE LPC1.ListingsID = LPC.ListingsID)
ORDER BY ChangeDate DESC;

However, if there are two rows for a single ListingsID value on the
same most-recent-date then it cannot choose between them, and you're
going to get both. You could choose to break this tie by showing the
larger or smaller NewPrice value (for example) but this too could be
duplicated, still leaving you with two rows for the same ListingID.
Or, perhaps, showing two rows for the same ListingsID value is not a
problem in this case.

This query only returns the LAST record in the table. I want it to return
ALL records from the table )(which contain duplicate ListingsIDs) based on
the last ListingID added for that group of records.

Thus instead of just returning:
ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

* It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

Is this possible: Here is how my query currently looks (below). Could I just
add a * to the end of TOP? But, alas that does not work:

PARAMETERS LID Long;
SELECT TOP 1 tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, tblListingsPriceChanges.ChangeDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR (((tblListingsPriceChanges.ListingsID)=[LID]))
ORDER BY tblListingsPriceChanges.ChangeDate DESC;
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
J

jason

Lance - thanks - but your query is throwing up syntax errors:

PARAMETERS LID Long;
SELECT tblListingsPriceChanges.ListingsID, tblListingsPriceChanges.NewPrice,
Max(tblListingsPriceChanges.ChangeDate) AS LatestDate

FROM tblListingsPriceChanges

WHERE ((([LID]) Is Null))
OR (((tblListingsPriceChanges.ListingsID)=[LID]))

GROUP by tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice

ORDER BY tblListingsPriceChanges.ChangeDate DESC;

Lance said:
SELECT tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, max
(tblListingsPriceChanges.ChangeDate) as LatestDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR
(((tblListingsPriceChanges.ListingsID)=[LID]))
Group by tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice
ORDER BY tblListingsPriceChanges.ChangeDate DESC;


hth,
Lance
-----Original Message-----
This query only returns the LAST record in the table. I want it to return
ALL records from the table )(which contain duplicate ListingsIDs) based on
the last ListingID added for that group of records.

Thus instead of just returning:
ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

* It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

Is this possible: Here is how my query currently looks (below). Could I just
add a * to the end of TOP? But, alas that does not work:

PARAMETERS LID Long;
SELECT TOP 1 tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, tblListingsPriceChanges.ChangeDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR (((tblListingsPriceChanges.ListingsID)=[LID]))
ORDER BY tblListingsPriceChanges.ChangeDate DESC;


.
Click to expand...
Click to expand...
 
J

jason

Hey Tom all I did was wrap a bracket ")" around MAX(ChangeDate to stop the
syntax errors. The query works - but it only returns one record.

- Jason

Tom Ellison said:
Dear Jason:

The problem would seem to be in how you "cleaned up" my query. Did it
have a problem as I originally presented it?

:) Progress - baby steps!

Tom, your query is only returning ONE record when it should be returning 3
records. Thus, if the PARAMETER is NULL it should return all valid UNIQUE
LISTINGID records selected by Max(). Any ideas?

But this is what I get:

ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From:

tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

But this is what I want:
It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

This is how I cleaned up the your query:

PARAMETERS LID Long;
SELECT ListingsID, NewPrice, ChangeDate

FROM tblListingsPriceChanges

WHERE ([LID] IS NULL OR ListingsID = [LID])

AND ChangeDate =

(SELECT MAX(ChangeDate) FROM tblListingsPriceChanges WHERE
tblListingsPriceChanges.ListingsID = [tblListingsPriceChanges.ListingsID])

ORDER BY ChangeDate DESC

news:[email protected]...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
J

jason

Hi - I got the brackets right but now it says it: "You are trying to execute
a query tht does note inlcue the specified expression tblListings.ChangeDate
as part of the aggregate function.......

PARAMETERS LID Long;
SELECT tblListingsPriceChanges.ListingsID, tblListingsPriceChanges.NewPrice,
Max(tblListingsPriceChanges.ChangeDate) AS LatestDate
FROM tblListingsPriceChanges
WHERE ([LID] Is Null OR tblListingsPriceChanges.ListingsID=[LID])
GROUP by tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice
ORDER BY tblListingsPriceChanges.ChangeDate DESC;



Lance said:
Looks like its because of the parenthesis. Your original
statement has 6 open parenthesis and 4 closing
parenthesis.

Regardless, you should be able to get the point. Use a
group by query with a max(changeDate).

Lance
-----Original Message-----
Lance - thanks - but your query is throwing up syntax errors:

PARAMETERS LID Long;
SELECT tblListingsPriceChanges.ListingsID, tblListingsPriceChanges.NewPrice,
Max(tblListingsPriceChanges.ChangeDate) AS LatestDate

FROM tblListingsPriceChanges

WHERE ((([LID]) Is Null))
OR (((tblListingsPriceChanges.ListingsID)=[LID]))

GROUP by tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice

ORDER BY tblListingsPriceChanges.ChangeDate DESC;

Lance said:
SELECT tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice, max
(tblListingsPriceChanges.ChangeDate) as LatestDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR
(((tblListingsPriceChanges.ListingsID)=[LID]))
Group by tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice
ORDER BY tblListingsPriceChanges.ChangeDate DESC;


hth,
Lance
-----Original Message-----
This query only returns the LAST record in the table. I
want it to return
ALL records from the table )(which contain duplicate
ListingsIDs) based on
the last ListingID added for that group of records.

Thus instead of just returning:
ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

* It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

Is this possible: Here is how my query currently looks
(below). Could I just
add a * to the end of TOP? But, alas that does not work:

PARAMETERS LID Long;
SELECT TOP 1 tblListingsPriceChanges.ListingsID,
tblListingsPriceChanges.NewPrice,
tblListingsPriceChanges.ChangeDate
FROM tblListingsPriceChanges
WHERE ((([LID]) Is Null)) OR
(((tblListingsPriceChanges.ListingsID)=[LID]))
ORDER BY tblListingsPriceChanges.ChangeDate DESC;


.
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Jason:

The aliases for the tables were removed as well. This would
definitely destroy the functionality of the query I provided. So, it
is not the case that all you did was to put brackets around a portion
of the query.

The aliases were LPC and LPC1. These are gone in your query. It
won't work without them.

Hey Tom all I did was wrap a bracket ")" around MAX(ChangeDate to stop the
syntax errors. The query works - but it only returns one record.

- Jason

Tom Ellison said:
Dear Jason:

The problem would seem to be in how you "cleaned up" my query. Did it
have a problem as I originally presented it?

:) Progress - baby steps!

Tom, your query is only returning ONE record when it should be returning 3
records. Thus, if the PARAMETER is NULL it should return all valid UNIQUE
LISTINGID records selected by Max(). Any ideas?

But this is what I get:

ListingID NewPrice ChangeDate
77 70,000 11/13/2004

From:

tblListingsPriceChanges:
ListingID NewPrice ChangeDate
28 70,000 05/10/2004
28 60,000 06/11/2004
32 50,000 02/13/2004
77 70,000 11/10/2004
77 60,000 11/11/2004
77 50,000 11/13/2004

But this is what I want:
It should actually return: *
28 60,000 06/11/2004
32 50,000 02/13/2004
77 50,000 11/13/2004

This is how I cleaned up the your query:

PARAMETERS LID Long;
SELECT ListingsID, NewPrice, ChangeDate

FROM tblListingsPriceChanges

WHERE ([LID] IS NULL OR ListingsID = [LID])

AND ChangeDate =

(SELECT MAX(ChangeDate) FROM tblListingsPriceChanges WHERE
tblListingsPriceChanges.ListingsID = [tblListingsPriceChanges.ListingsID])

ORDER BY ChangeDate DESC

news:[email protected]...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Advanced problem using auto-number and datediff to find readmissio 2
LOOKUP function between two dates in Excel 2007 6
LOOKUP within a 1
Returning a Date with Multiple Criteria Lookup Values 0
Autofil on variable rows, delete extract and show remaining rows 3
How to get the array like this? 6
Query to show top x% by group 11
top (latest) record for each group 2

Top