Need Help With SQL Statement

------=_NextPart_000_0007_01C5865D.57996B80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I can do basic SQL coding but I am having a hard time figuring this one out.

I have a table with the following fields.

CREATE TABLE sop (
sop_id bigint(12) NOT NULL auto_increment,
certcode_code varchar(5) NOT NULL default '',
sop_index int(5) NOT NULL default '0',
sop_versionmajor int(3) NOT NULL default '0',
sop_versionminor int(3) NOT NULL default '0',
sop_versionfix int(2) NOT NULL default '0',
sop_date date NOT NULL default '0000-00-00',
sop_title varchar(35) NOT NULL default '',
sop_text longtext NOT NULL,
PRIMARY KEY (sop_id)
)

The index is a number that acts like a catalog number in a library. There
can be multiple occurrences of records with the same index number. The
version information will determine which is the latest. The date is
incidental to the other sequencing fields.

What I would like to do is create a SQL statement that will retrieve all of
the latest versions for each index number. My thought was

SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
sop_versionfix DESC

That would bring the latest/newest versions to the top. But I want it in
index order ASC. If I add sop_index to the ORDER BY I will have it in
reverse order for printing. Or if I add ASC then I have to find the last
version of each index.

Any ideas, suggestions, coding is GREATLY appreciated.

Thanks in advance.

Mike



------=_NextPart_000_0007_01C5865D.57996B80
Content-Type: text/plain; charset=us-ascii


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
------=_NextPart_000_0007_01C5865D.57996B80--

Re: Need Help With SQL Statement

Hi,
Maybe you can try

SELECT * FROM sop ORDER BY sop_index ASC, sop_versionmajor DESC,
sop_versionminor DESC, sop_versionfix DESC

Hope this works...

Sam

-----Original Message-----
From: "Michael Avila"
To: "MySQL - Win32"
Date: Mon, 11 Jul 2005 21:13:10 -0400
Subject: Need Help With SQL Statement

> I can do basic SQL coding but I am having a hard time figuring this one
> out.
>
> I have a table with the following fields.
>
> CREATE TABLE sop (
> sop_id bigint(12) NOT NULL auto_increment,
> certcode_code varchar(5) NOT NULL default '',
> sop_index int(5) NOT NULL default '0',
> sop_versionmajor int(3) NOT NULL default '0',
> sop_versionminor int(3) NOT NULL default '0',
> sop_versionfix int(2) NOT NULL default '0',
> sop_date date NOT NULL default '0000-00-00',
> sop_title varchar(35) NOT NULL default '',
> sop_text longtext NOT NULL,
> PRIMARY KEY (sop_id)
> )
>
> The index is a number that acts like a catalog number in a library.
> There
> can be multiple occurrences of records with the same index number. The
> version information will determine which is the latest. The date is
> incidental to the other sequencing fields.
>
> What I would like to do is create a SQL statement that will retrieve
> all of
> the latest versions for each index number. My thought was
>
> SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
> sop_versionfix DESC
>
> That would bring the latest/newest versions to the top. But I want it
> in
> index order ASC. If I add sop_index to the ORDER BY I will have it in
> reverse order for printing. Or if I add ASC then I have to find the
> last
> version of each index.
>
> Any ideas, suggestions, coding is GREATLY appreciated.
>
> Thanks in advance.
>
> Mike
>
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Need Help With SQL Statement

What about :

SELECT
sop_id,certcode_code,sop_index,sop_versionmajor,sop_version_ minor,sop_versio
nfix,sop_date,sop_title,sop_text,(sop_version_major*100+sop_ versionminor*10+
sop_versionfix) AS "sop_sort" FROM sop ORDER BY sop_sort DESC

or similar (fudge the multipliers based on your expected maxima.)


----- Original Message -----
From: "Michael Avila"
To: "MySQL - Win32"
Sent: Tuesday, July 12, 2005 11:13 AM
Subject: Need Help With SQL Statement


> I can do basic SQL coding but I am having a hard time figuring this one
out.
>
> I have a table with the following fields.
>
> CREATE TABLE sop (
> sop_id bigint(12) NOT NULL auto_increment,
> certcode_code varchar(5) NOT NULL default '',
> sop_index int(5) NOT NULL default '0',
> sop_versionmajor int(3) NOT NULL default '0',
> sop_versionminor int(3) NOT NULL default '0',
> sop_versionfix int(2) NOT NULL default '0',
> sop_date date NOT NULL default '0000-00-00',
> sop_title varchar(35) NOT NULL default '',
> sop_text longtext NOT NULL,
> PRIMARY KEY (sop_id)
> )
>
> The index is a number that acts like a catalog number in a library. There
> can be multiple occurrences of records with the same index number. The
> version information will determine which is the latest. The date is
> incidental to the other sequencing fields.
>
> What I would like to do is create a SQL statement that will retrieve all
of
> the latest versions for each index number. My thought was
>
> SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
> sop_versionfix DESC
>
> That would bring the latest/newest versions to the top. But I want it in
> index order ASC. If I add sop_index to the ORDER BY I will have it in
> reverse order for printing. Or if I add ASC then I have to find the last
> version of each index.
>
> Any ideas, suggestions, coding is GREATLY appreciated.
>
> Thanks in advance.
>
> Mike
>
>
>


------------------------------------------------------------ ----------------
----


>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=lsharpe@pacificwireless.c om.au


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Need Help With SQL Statement

Thanks! Did not realize that sorts could be broken out like that. Thank
you.

Mike

-----Original Message-----
From: Sam @ Inmagine [mailto:sam@inmagine.com]
Sent: Monday, July 11, 2005 9:33 PM
To: Michael Avila; MySQL - Win32
Subject: Re: Need Help With SQL Statement


Hi,
Maybe you can try

SELECT * FROM sop ORDER BY sop_index ASC, sop_versionmajor DESC,
sop_versionminor DESC, sop_versionfix DESC

Hope this works...

Sam

-----Original Message-----
From: "Michael Avila"
To: "MySQL - Win32"
Date: Mon, 11 Jul 2005 21:13:10 -0400
Subject: Need Help With SQL Statement

> I can do basic SQL coding but I am having a hard time figuring this one
> out.
>
> I have a table with the following fields.
>
> CREATE TABLE sop (
> sop_id bigint(12) NOT NULL auto_increment,
> certcode_code varchar(5) NOT NULL default '',
> sop_index int(5) NOT NULL default '0',
> sop_versionmajor int(3) NOT NULL default '0',
> sop_versionminor int(3) NOT NULL default '0',
> sop_versionfix int(2) NOT NULL default '0',
> sop_date date NOT NULL default '0000-00-00',
> sop_title varchar(35) NOT NULL default '',
> sop_text longtext NOT NULL,
> PRIMARY KEY (sop_id)
> )
>
> The index is a number that acts like a catalog number in a library.
> There
> can be multiple occurrences of records with the same index number. The
> version information will determine which is the latest. The date is
> incidental to the other sequencing fields.
>
> What I would like to do is create a SQL statement that will retrieve
> all of
> the latest versions for each index number. My thought was
>
> SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
> sop_versionfix DESC
>
> That would bring the latest/newest versions to the top. But I want it
> in
> index order ASC. If I add sop_index to the ORDER BY I will have it in
> reverse order for printing. Or if I add ASC then I have to find the
> last
> version of each index.
>
> Any ideas, suggestions, coding is GREATLY appreciated.
>
> Thanks in advance.
>
> Mike
>
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Need Help With SQL Statement

I just had a similar situation where I need to get the latest values. I
have included some syntax below. The end result of the query gives the
Maximum (in my case the latest version number of the field) the resulting
query only has one value for each field entry.

SELECT Max(Table.DLLVersion), Table.DLL AS MaxOfDLLVersion FROM Table GROUP
By Table.DLL


Example data source;
FieldVersion , DLL
1 , UserInterface
1 , DataAccess
1 , Licence
2 , UserInterFace
2 , DataAccess
3 , UserInterface
4 , UserInterFace


The result of the Query would be...
MaxOfDLLVersion , DLL
1 , Licence
2 , DataAccess
3 , UserInterface



Regards,
Justin Elward

Blue Wave Software Pty Limited
justin@bluewavesoftware.com.au


> -----Original Message-----
> From: Michael Avila [mailto:mavila@mich.com]
> Sent: Tuesday, 12 July 2005 11:13 AM
> To: MySQL - Win32
> Subject: Need Help With SQL Statement
>
> I can do basic SQL coding but I am having a hard time figuring this one
> out.
>
> I have a table with the following fields.
>
> CREATE TABLE sop (
> sop_id bigint(12) NOT NULL auto_increment,
> certcode_code varchar(5) NOT NULL default '',
> sop_index int(5) NOT NULL default '0',
> sop_versionmajor int(3) NOT NULL default '0',
> sop_versionminor int(3) NOT NULL default '0',
> sop_versionfix int(2) NOT NULL default '0',
> sop_date date NOT NULL default '0000-00-00',
> sop_title varchar(35) NOT NULL default '',
> sop_text longtext NOT NULL,
> PRIMARY KEY (sop_id)
> )
>
> The index is a number that acts like a catalog number in a library. There
> can be multiple occurrences of records with the same index number. The
> version information will determine which is the latest. The date is
> incidental to the other sequencing fields.
>
> What I would like to do is create a SQL statement that will retrieve all
> of
> the latest versions for each index number. My thought was
>
> SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
> sop_versionfix DESC
>
> That would bring the latest/newest versions to the top. But I want it in
> index order ASC. If I add sop_index to the ORDER BY I will have it in
> reverse order for printing. Or if I add ASC then I have to find the last
> version of each index.
>
> Any ideas, suggestions, coding is GREATLY appreciated.
>
> Thanks in advance.
>
> Mike
>




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Need Help With SQL Statement

If you actually only want the latest version for each sop_id and not any
older versions then you need to follow the idea given in section 3.6.2
in the MySQL manual. The example given is

SELECT article, dealer, price
FROM shop
WHERE price=3D(SELECT MAX(price) FROM shop);

In your case it would go something like this

SELECT sop_id, certcode_code, sop_index, sop_versionmajor,
sop_version_minor, sop_versionfix, sop_date, sop_title, sop_text
FROM sop AS t1
WHERE sop_version_major*100 + sop_versionminor*10 +
sop_versionfix =3D (
SELECT MAX(sop_version_major*100 + sop_versionminor*10 +
sop_versionfix)=20
FROM sop as t2=20
WHERE t1.sop_id =3D t2.sop_id
)

John Bonnett

-----Original Message-----
From: Leigh Sharpe [mailto:lsharpe@pacificwireless.com.au]=20
Sent: Tuesday, 12 July 2005 11:13 AM
To: Michael Avila; MySQL - Win32
Subject: Re: Need Help With SQL Statement

What about :

SELECT
sop_id,certcode_code,sop_index,sop_versionmajor,sop_version_ minor,sop_ve
rsio
nfix,sop_date,sop_title,sop_text,(sop_version_major*100+sop_ versionminor
*10+
sop_versionfix) AS "sop_sort" FROM sop ORDER BY sop_sort DESC

or similar (fudge the multipliers based on your expected maxima.)


----- Original Message -----=20
From: "Michael Avila"
To: "MySQL - Win32"
Sent: Tuesday, July 12, 2005 11:13 AM
Subject: Need Help With SQL Statement


> I can do basic SQL coding but I am having a hard time figuring this
one
out.
>
> I have a table with the following fields.
>
> CREATE TABLE sop (
> sop_id bigint(12) NOT NULL auto_increment,
> certcode_code varchar(5) NOT NULL default '',
> sop_index int(5) NOT NULL default '0',
> sop_versionmajor int(3) NOT NULL default '0',
> sop_versionminor int(3) NOT NULL default '0',
> sop_versionfix int(2) NOT NULL default '0',
> sop_date date NOT NULL default '0000-00-00',
> sop_title varchar(35) NOT NULL default '',
> sop_text longtext NOT NULL,
> PRIMARY KEY (sop_id)
> )
>
> The index is a number that acts like a catalog number in a library.
There
> can be multiple occurrences of records with the same index number. The
> version information will determine which is the latest. The date is
> incidental to the other sequencing fields.
>
> What I would like to do is create a SQL statement that will retrieve
all
of
> the latest versions for each index number. My thought was
>
> SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
> sop_versionfix DESC
>
> That would bring the latest/newest versions to the top. But I want it
in
> index order ASC. If I add sop_index to the ORDER BY I will have it in
> reverse order for printing. Or if I add ASC then I have to find the
last
> version of each index.
>
> Any ideas, suggestions, coding is GREATLY appreciated.
>
> Thanks in advance.
>
> Mike
>
>
>


------------------------------------------------------------ ------------
----
----


>
> --=20
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=3Dlsharpe@pacificwireless .com.au



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org