Max function in a query 2

[matrun]

Hi

Can anyone help? I'm trying to find the latest date from a field along with the unique identifier against that instance. So, I have a list of release dates, barcodes all sharing the same artist name; how would I extract the barcode, release date (and artist name) for the record with the latest date? I try this:

SELECT EMIFile.Bcode, EMIFile.Product_Artist, Max(EMIFile.Reldate)
FROM EMIFile
WHERE (((EMIFile.Product_Artist)="UB40&quot);

...which according to Access should do it, but get a message that it can't find "...barcode as part of an aggragate function". I.e. it doesn't allow mixing of a group by and simple select query, it would appear. Any ideas how I can get around this? I've been told the SQL above is OK

Thanks

Matt

 

[nicsin]

it is not a simple select query since you are using an aggregate function -

Max()

.

try this:

SELECT EMIFile.Bcode, EMIFile.Product_Artist, Max(EMIFile.Reldate) 
FROM EMIFile
WHERE (((EMIFile.Product_Artist)="UB40") 
group by EMIFile.Bcode, EMIFile.Product_Artist);

 

[Golom]

Try something like

SELECT E.Bcode, E.Product_Artist, E.Reldate

FROM EMIFile E

WHERE E.RelDate IN 
          (Select MAX(RelDate) From EMIFile
           Where EmiFile.Product_Artist = "UB40")

 

[nicsin]

Yes that's it!

 

[matrun]

Thanks guys!

The first one gets a syntax error - missing operator - response; I can't see anything bad there but assume it must be a missing bracket?

Second one seems to return a result but does include an artist which doesn't have the name UB40; don't understand how that could happen when name was so specifically stipulated?

Any further ideas?

Thanks again for that feedback; definitely seems on the right track!

M

 

[nicsin]

yes there is a syntax error in my query but forget about it. Golom's should work fine! what is the name of the artist you refer to?

 

[matrun]

Hi Nicsin - thanks for that; scrapped the extra bracket and got a result. However, result lists all those records with artist = UB40 and the release date against each one plus barcode. However, I'm after getting ONLY the latest release date, with the artist, barcode and release date. Ideally, I want to see:

Bcode Product_Artist Expr1002
0724381267523 UB40 19/08/2002

...but am getting:

Bcode Product_Artist Expr1002
0724381267523 UB40 19/08/2002
0724381129821 UB40 22/10/2001
0724385042423 UB40 23/10/2000
0724384646929 UB40 12/10/1998
0724384542429 UB40 27/04/1998
0724384440220 UB40 30/06/1997
0724384093723 UB40 30/10/1995
0077778639121 UB40 24/10/1994
0724383068425 UB40 17/10/1994
0077778627128 UB40 27/09/1993
0077778826125 UB40 27/09/1993
0077778822929 UB40 12/07/1993
0077778638728 UB40 02/09/1992
0077778649526 UB40 10/08/1992
0077778644828 UB40 05/08/1992
0077778638827 UB40 05/08/1992
0077778638926 UB40 05/08/1992
0077778644422 UB40 05/08/1992
0077778627029 UB40 05/08/1992
0077778644729 UB40 05/08/1992
0077778644620 UB40 05/08/1992
0077778641223 UB40 24/06/1992
0077778632429 UB40 01/01/1992
0077778632221 UB40 01/01/1992

Cheers!

M

 

[matrun]

Hi - artist is UB40. It's just a random sample I put there to get the most recent release date. Golom's is good but the results return an artist <> UB40, i.e.

Bcode Product_Artist Reldate
0724381267523 UB40 19/08/2002
0724381313428 Beenie Man 19/08/2002

&quot;Beenie Man&quot;

Actually; just tried 'designing' it further and adding an extra reference to the artist seems to tie it up, despite there being a reference there already in the 1st where clause!!

SELECT E.Bcode, E.Product_Artist, E.Reldate
FROM EMIFile AS E
WHERE (((E.Product_Artist)=&quot;UB40&quot AND ((E.Reldate) In (Select MAX(RelDate) From EMIFile
Where EmiFile.Product_Artist = &quot;UB40&quot));



Seems to work now! Thanks a million

Matt

 

[nicsin]

Matrun,

as you noticed this query does not get you what you want because it is grouping by 2 fields and thus by all combinations. Please use Golom's query as it is correct!

 

[nicsin]

Now that I am thinking about it...try this:

SELECT E.Bcode, E.Product_Artist, E.Reldate

FROM EMIFile E

WHERE E.RelDate IN 
          (Select MAX(RelDate) From EMIFile
           Where EmiFile.Product_Artist = "UB40") 
  and E.Product_Artist = "UB40"

 

[matrun]

Cheers Nicson/Golom; all fixed!

 

[Golom]

nicsin

Thanks for fixing my screw-up. Too early in the morning in my time zone I guess.

 

[nicsin]

It's my honor Golom... I've actually just finished work and having my evening coffee. I suggest you do the same ;-)