I'm new to mysql and I was wondering if I could trouble some of you for
help. I have a table in my database that may contain multiple versions
of the same element as different rows.
I wanted to use the following query to group the rows by element with
"group by" and then use the "having" to get the most recent version of
each element, which would then be returned in "select" but this doesn't
seem to be working. Any elements with multiple versions are simply not
returned.
I could swear that I have used a similar approach in the distant past
with other databases and it worked, though, so I must be bollocksing
this up pretty badly while attempting to work off of what I
remember...in any case, any help on understanding why this isn't
working would be appreciated.
select
element_id -- not unique!
,created_on
,name
,description
from
elementversions
group by
element_id
having
created_on = max(created_on) 2 1560
"rubikzube* " <th************ @gmail.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. . I wanted to use the following query to group the rows by element with "group by" and then use the "having" to get the most recent version of each element
The HAVING clause creates criteria for restricting groups from the query,
not for restricting individual rows within the groups. It's useful for
doing things like HAVING MAX(created_on) > '2005-12-31'. Row restrictions
are done in the WHERE clause, but unfortunately, aggregate functions like
MAX cannot be used in the WHERE clause.
select element_id -- not unique! ,created_on ,name ,description from elementversions group by element_id having created_on = max(created_on)
Here's a different solution, using an outer join trick. This fetches
elements for which there is no other element with the same id and a greater
created_on date.
SELECT e.element_id, e.created_on, e.name, e.description
FROM elementversions AS e LEFT OUTER JOIN elementversions AS egreater
ON e.element_id = egreated.elemen t_id AND e.created_on <
egreater.create d_on
WHERE egreater.elemen t_id IS NULL
If the egreater.elemen t_id is NULL, it means nothing matched on the right
side of our join. If there is no record with a greater created_on date,
then `e` must be the record with the greatest created_on for the given
element_id.
Regards,
Bill K.
rubikzube* wrote: I'm new to mysql and I was wondering if I could trouble some of you for help. I have a table in my database that may contain multiple versions of the same element as different rows.
I wanted to use the following query to group the rows by element with "group by" and then use the "having" to get the most recent version of each element, which would then be returned in "select" but this doesn't seem to be working. Any elements with multiple versions are simply not returned.
I could swear that I have used a similar approach in the distant past with other databases and it worked, though, so I must be bollocksing this up pretty badly while attempting to work off of what I remember...in any case, any help on understanding why this isn't working would be appreciated.
select element_id -- not unique! ,created_on ,name ,description from elementversions group by element_id having created_on = max(created_on)
how about :
select a.element_id,a. created_on,a.na me,a.descriptio n from elementversions
a,
(select b.element_id,ma x(b.created_on) as created_on from elementversions
b) c
where a.element_id=c. element_id and a.created_on=c. created_on;
if you tried to do this by just adding the max(created_on) , you could get
multiple records returned as you would need to include name and
description in the group by. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tor Hovland |
last post by:
I'm trying to transform the document element of incoming xml files, however,
I'm having trouble with namespace references not appearing correctly. Here's
an example input file:
<?xml version="1.0" encoding="utf-8" ?>
<inputdoc xmlns:a="http://www.dummy.org" something="true">
<hello somethingelse="false" />
<foo>
<!-- a comment -->
<bar />
|
by: soup_or_power |
last post by:
Hi
Sorry about the heading.
I have a table with td consisting of lists with <select></select>. When
I do a document.getElementById("element").innerHTML I don't see the
selected item. IOW, the innerHTML is not dynamic. Is there some way to
get the most recent selected without traversing through the list's
options.
Thank you
|
by: bibsoconner |
last post by:
Hi,
I hope someone can please help me. I'm having a lot of trouble with
schema files in .NET. I have produced a very simple example that uses
"include" to include other schema files. It all works with SPY, but
when I pick "Validate Schema" from the .NET 2003 menu, it fails with
message:
"Type XType is not declared." As I hinted at in my subject line, I
suspect that it has to do with including another schema multiple times.
|
by: Daniel Walzenbach |
last post by:
Hi,
I have a web application which sometimes throws an “out of memory”
exception. To get an idea what happens I traced some values using performance
monitor and got the following values (for one day):
\\FFDS24\ASP.NET Applications(_LM_W3SVC_1_Root_ATV2004)\Errors During
Execution: 7
\\FFDS24\ASP.NET Apps v1.1.4322(_LM_W3SVC_1_Root_ATV2004)\Compilations
|
by: clare at snyder.on.ca |
last post by:
I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every renewal in
the history of the policyholder.
The information is in 2 tables, policy and customer, which share the
custid data. The polno changes with every renewal Renewals in 2004
would be D, 2005 S, and 2006 L. polexpdates for a given customer...
| |
by: eyal.susser |
last post by:
Hi,
I'm trying to write a script that uses diffxml along with 4xupdate
to merge XML files. So far, I'm having trouble with even the most
basic files. I have tried different versions of the components, to no
avail. Are there any alternatives? I've put sample files below.
Thanks,
Eyal.
|
by: weston |
last post by:
I'm making a foray into trying to create custom vertical scrollbars and
sliders, and thought I had a basic idea how to do it, but seem to be
having some trouble with the implementation.
My thinking was:
(a) create a div for the slider / scroll nub to move within
(b) attach event handlers which, onmousedown, specify the slider/nub is
moveable, and onmouseup, specify it's not
(c) attach an event handler to the contaning div which,...
|
by: tschulken |
last post by:
I have a query where i need to look for a value of a lower level xml
element based on the value of a parent element existing first. Here is
a simple example of the xml
<S3Client>
<Buttons>
<Button>Activity
<RestrictedClientType>
<ClientType>02</ClientType>
</RestrictedClientType>
|
by: matheussousuke |
last post by:
Hello, I'm using tiny MCE plugin on my oscommerce and it is inserting my website URL when I use insert image function in the emails.
The goal is: Make it send the email with the URL http://mghospedagem.com/images/controlpanel.jpg
instead of
http://mghospedagem.comhttp://mghospedagem.com/images/controlpanel.jpg
As u see, there's the website URL before the image URL.
|
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: 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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |