Hello all,
I have the following data, that was queried and sorted to columns
PROBLEM_ID and then by STATUSDATE (ascending):
STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ----------
10 12/04/2005 1234
40 15/05/2005 1234
10 11/11/2004 1235
50 15/12/2004 1235
70 20/01/2005 1235
I now want another query which returns the row which is always the
latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLE M_ID, Last(qryFirst.S TATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLE M_ID;
However, when I want to add qryFirst.STATUS DATE to the SELECT clause, I
get an error ,,You tried to execute a query that does not include the
specified expression <name> as part of an aggregate function. (Error
3122)"
I need to see what the STATUSDATE is for each of the records returned
in the second query. How do I do this? Please!!!!
Kind Regards,
Jean 8 10309
On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@h otmail.com>
wrote:
Classic problem. I always solve it by using two queries. The first one
(let's call it Q2) gets the Max(StatusDate) group by ProblemID.
The second one joins the original query Q1 and Q2 by both the
StatusDate and ProblemID fields, and then you can additionally select
StatusID and any other column Q1 might have.
Btw, note the use of Max rather than Last. I find it more predictable,
and it is more universally part of the SQL language.
-Tom. Hello all,
I have the following data, that was queried and sorted to columns PROBLEM_ID and then by STATUSDATE (ascending):
STATUS_ID STATUSDATE PROBLEM_ID --------- ---------- ---------- 10 12/04/2005 1234 40 15/05/2005 1234 10 11/11/2004 1235 50 15/12/2004 1235 70 20/01/2005 1235
I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLE M_ID, Last(qryFirst.S TATUS_ID) AS LAST_STATUS FROM qryFirst GROUP BY qryFirst.PROBLE M_ID;
However, when I want to add qryFirst.STATUS DATE to the SELECT clause, I get an error ,,You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"
I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!
Kind Regards,
Jean
On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no************ *@cox.net>
wrote: On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@h otmail.com> wrote:
Classic problem. I always solve it by using two queries. The first one (let's call it Q2) gets the Max(StatusDate) group by ProblemID. The second one joins the original query Q1 and Q2 by both the StatusDate and ProblemID fields, and then you can additionally select StatusID and any other column Q1 might have.
Btw, note the use of Max rather than Last. I find it more predictable, and it is more universally part of the SQL language.
To add to what Tom said, the First() and Last() functions are unfortunately
named. The thing either of them does is to return an arbitrary value from the
group, with nothing particularly "first" or "last" about it. I presume these
functions actually do something like what their names imply if you are
querying a table linked to an ISAM data source in which physical order is
considered meaningful, such as Paradox.
Steve Jorgensen <no****@nospam. nospam> wrote in
news:qk******** *************** *********@4ax.c om: On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no************ *@cox.net> wrote:
On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@h otmail.com> wrote:
Classic problem. I always solve it by using two queries. The first one (let's call it Q2) gets the Max(StatusDate) group by ProblemID. The second one joins the original query Q1 and Q2 by both the StatusDate and ProblemID fields, and then you can additionall y select StatusID and any other column Q1 might have.
Btw, note the use of Max rather than Last. I find it more predictable , and it is more universally part of the SQL language.
To add to what Tom said, the First() and Last() functions are unfortunately named. The thing either of them does is to return an arbitrary value from the group, with nothing particularly "first" or "last" about it. I presume these functions actually do something like what their names imply if you are querying a table linked to an ISAM data source in which physical order is considered meaningful, such as Paradox.
Well, First() means the first value for that column in the recordset
in its present order, Last() means the last value.
It's no different than the fact that TOP 10 on a query sorted in
descending data order will return a different set of records than
TOP 10 on the same query sorted by CompanyName ascending.
It should be self-evident that the terms First() and Last() are
dependent on the ordering of the domain to which they are applied.
Why this confuses people into saying that they return
non-predictable results, I can't say. It's only non-predictable if
you're ignoring the sort order.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
On Tue, 19 Jul 2005 14:16:52 -0500, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote: Steve Jorgensen <no****@nospam. nospam> wrote in news:qk******* *************** **********@4ax. com:
On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no************ *@cox.net> wrote:
On 19 Jul 2005 04:26:29 -0700, "Jean" <je**********@h otmail.com> wrote:
Classic problem. I always solve it by using two queries. The first one (let's call it Q2) gets the Max(StatusDate) group by ProblemID. The second one joins the original query Q1 and Q2 by both the StatusDate and ProblemID fields, and then you can additional ly select StatusID and any other column Q1 might have.
Btw, note the use of Max rather than Last. I find it more predictabl e, and it is more universally part of the SQL language.
To add to what Tom said, the First() and Last() functions are unfortunately named. The thing either of them does is to return an arbitrary value from the group, with nothing particularly "first" or "last" about it. I presume these functions actually do something like what their names imply if you are querying a table linked to an ISAM data source in which physical order is considered meaningful, such as Paradox.
Well, First() means the first value for that column in the recordset in its present order, Last() means the last value.
It's no different than the fact that TOP 10 on a query sorted in descending data order will return a different set of records than TOP 10 on the same query sorted by CompanyName ascending.
It should be self-evident that the terms First() and Last() are dependent on the ordering of the domain to which they are applied. Why this confuses people into saying that they return non-predictable results, I can't say. It's only non-predictable if you're ignoring the sort order.
It is, in fact self-evident until you realize there's no way to specify the
sort that you're talking about. Aggregate functions, including First() and
Last() return an item from within the group, but you can only sort a query on
fields on the group by clause or on aggregate results.
For example, let's say I have the following...
tblPerson
PersonID
FullName
StreetAddress
City
State
ZipCode
Now, let's say I want the first full name in alphabetical order for each Zip
code...
SELECT ZipCode,
First(FullName) As FirstFullName
FROM tblPerson
GROUP BY ZipCode
ORDER BY ZipCode
So - what does First() mean? If you say that it should be in the sort order,
well, the sort order only sorts ZipCode groups. If I try to add FullName to
the sort, that's an error because FullName is not part of the GROUP BY. If I
try to solve that by adding FullName to the GROUP BY, that of course, defeats
the whole purpose because I no longer have a single group for each ZipCode.
There is simply no way in SQL to specify a sort order by which First or Last
within a group should be identified.
Thanks for the input guys, and for clearing that up with the FIRST and
LAST functions.
I actually tried FIRST/LAST in some way and got it right, but then I
had to use the FIRST function instead of the LAST. Having got it right
I thought cool, but WHY FIRST???
So I tried MAX as you suggested, and it still works.
Tom, I tried your suggestion, and compared it to mine, and it gives the
same results. Thanks.
This is what I did:
SELECT qryFirst.PROBLE M_ID, MAX(qryFirst.ST ATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLE M_ID;
I will use your second query suggestion now to get the STATUSDATE.
Regards,
Jean
Steve Jorgensen wrote: On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no************ *@cox.net> wrote: To add to what Tom said, the First() and Last() functions are unfortunately named. The thing either of them does is to return an arbitrary value from the group, with nothing particularly "first" or "last" about it. I presume these functions actually do something like what their names imply if you are querying a table linked to an ISAM data source in which physical order is considered meaningful, such as Paradox.
I thought that if the source was sorted then you got the Min or Max?
ie, these are equivalent:
SELECT Max(Value) AS A FROM MTable
SELECT Last(Value) AS A FROM [SELECT * FROM MTable ORDER BY Value]. AS S
From this it is obviously easier to use the Max as without the Sort,
Last is not goin to work predictably.
My understanding was that in a newly compacted DB that the tables were
physically sorted by the PK, so there is an order. Of course, as soon as
you start adding/deleting/editing that the order is no longer guaranteed.
Personally, I only use First or Last when I need to include a field in
an Aggregate/Transform query that isn't part of the
Grouping/Count/Sum/etc.
--
Bri
On Tue, 19 Jul 2005 16:23:21 GMT, Bri <no*@here.com > wrote:
Steve Jorgensen wrote: On Tue, 19 Jul 2005 06:35:51 -0700, Tom van Stiphout <no************ *@cox.net> wrote: To add to what Tom said, the First() and Last() functions are unfortunately named. The thing either of them does is to return an arbitrary value from the group, with nothing particularly "first" or "last" about it. I presume these functions actually do something like what their names imply if you are querying a table linked to an ISAM data source in which physical order is considered meaningful, such as Paradox. I thought that if the source was sorted then you got the Min or Max?
ie, these are equivalent: SELECT Max(Value) AS A FROM MTable SELECT Last(Value) AS A FROM [SELECT * FROM MTable ORDER BY Value]. AS S
That was not my impression, but I have never tested it. Supposedly, the
sorting of any source is ignored when it is used in the FROM clause of another
query. In fact, you can see this at work if you just save one query with a
sort, then run a query of that query with no sort. The output generally looks
the same as doing a single query with no sort. Also, what would the order of
a FROM source mean if the FROM clause is a join of multiple sources? Which
sort would be definitive? From this it is obviously easier to use the Max as without the Sort, Last is not goin to work predictably.
My understanding was that in a newly compacted DB that the tables were physically sorted by the PK, so there is an order. Of course, as soon as you start adding/deleting/editing that the order is no longer guaranteed.
Personally, I only use First or Last when I need to include a field in an Aggregate/Transform query that isn't part of the Grouping/Count/Sum/etc.
Steve Jorgensen wrote: I thought that if the source was sorted then you got the Min or Max?
ie, these are equivalent: SELECT Max(Value) AS A FROM MTable SELECT Last(Value) AS A FROM [SELECT * FROM MTable ORDER BY Value]. AS S
That was not my impression, but I have never tested it. Supposedly, the sorting of any source is ignored when it is used in the FROM clause of another query. In fact, you can see this at work if you just save one query with a sort, then run a query of that query with no sort. The output generally looks the same as doing a single query with no sort. Also, what would the order of a FROM source mean if the FROM clause is a join of multiple sources? Which sort would be definitive?
I see where you are going with this and it does make sense. But if the
sort is of no importance then what possible use could First/Last ever
have? Hmm, thought I should see what Help has to say about it and it
does seem to say that the sort is relevant:
"Because records are usually returned in no particular order (unless the
query includes an ORDER BY clause), the records returned by these
functions will be arbitrary."
Min/Max are much easier and predictable to use, so I'm not likely to
switch over. It just seems odd that First/Last exist if they don't
actually do anything meaningful.
--
Bri This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: malcolm |
last post by:
Example, suppose you have these 2 tables
(NOTE: My example is totally different, but I'm simply trying to setup
the a simpler version, so excuse the bad design; not the point here)
CarsSold {
CarsSoldID int (primary key)
MonthID int
DealershipID int
NumberCarsSold int
|
by: Christian Wetzig |
last post by:
hi,
i have a table with test_field of type "TEXT". when i do:
select test_field from test where id=1;
the connection (with psql) hangs and no output is received, so i have to
kill psql.
this occurs, at first sight, if length(test_field)>32748.
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: William Wisnieski |
last post by:
Hello Everyone,
I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box...
|
by: GreatAlterEgo |
last post by:
Hi,
This is my query which is embedded in a COBOL program.
EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
| |
by: Henning M |
last post by:
Hi all,
I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!
"Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"
But when I try it from my vb.net app, I get ALL the records in the tabel??
What goes wrong?
I haven't been able to find any info on the net, besides others having
|
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient
moConn.Mode = adModeReadWrite' or using default...same result
|
by: AWasilenko |
last post by:
I can't figure out this problem Im having, I just can't understand why
it is ignoring the call I put in. First the code (This is a cherrypy
website):
import sys, cherrypy, html
class Root:
@cherrypy.expose
def index(self, pageid = "Index"):
selection = html.Page()
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |