h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Can't beleive I can't do this, was SO easy in VFP but

I want a query that groups the records, finds the max date and returns the primary key of the field with the maximum date.

But can I suss how to do it?

Anyone?

CH

With a table (called DateTable) with three fields:
lngID: autonumber, key field
dtmDate: date & time
txtWhatever: string

try this:
SELECT TOP 1 DateTable.lngID, DateTable.dtmDate, DateTable.txtWhatever
FROM DateTable
ORDER BY DateTable.dtmDate DESC;

If I fill a couple of records in with different dates, it returns a single record - with the highest date of the ones entered...

!

looking good.

Cheers - In good old VFp if you did a Group By query and left one field without anything such as Sum, Max etc it would bung in the relvant field.

Now of course I have to work out what of the many test queries I did this morning I can bin



CH

My next problem is of course where I have two identical dates, the query rightly returns both - I need to cream off just one - the end result is a web page where the latest news item gets the full details, the next 5 in a repeat region with summary details.

Ho hum

CH

Surely if you use a query such as that in post 2, you will only get one result? If "select top " not supported (I seem to recall it did not work with SQL Server 6.5), can you not read read the results of the query into a recordset and just use the first record? something like (in ASP vbscript):-

set conDB = Server.CreateObject("ADODB.Connection")
conDB.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & cstr(strUser) & ";Data Source=" & cstr(strServ) & ";Use Procedure for Prepare=0;Auto Translate=True;Packet Size=4096;User Id=" & cstr(strUser) & ";PASSWORD=" & cstr(strPass) & ";Initial Catalog=" & cstr(strDB) & ";"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "Select....(your query)",conDB
strDate = rs.fields("date")
strText = rs.Fields("Text")
...etc
rs.close

alas my ASP is so poor I cannot start adding stuff in - I got round it by having a second query that just selected the last record. I much prefer to get my recordsets right in Access then simply read them into my ASP pages.

Cheers all.

CH

Interestingly enough, I also thought that the query in #2 would only give you one record - but it apparently gives you one top date which means that if there are more records with that date, you get more records out...

I was going to suggest something like your code (only using Jet rather than ASP which I haven't got 'round to () - but only because I couldn't think of a query that did it right.

I always prefer doing things as much as possibly in queries - often is quicker and certainly (at least to my way of thinking) is easier to document...

I'm off for vacation, which will largely be offline and offPC so I guess I won't be doing too much query thinking...

have fun on your hols.

Thanks for the help, pages up working and boss is chuffed to bits

CH

-