simple query question

Greetings,

Table Structure
===============
Item - varchar
Ver - int


Table_A with Data
=================
Item | Ver
----------
A | 1
B | 1
B | 2
C | 1
D | 1
A | 3
B | 3
D | 2
D | 3
A | 4
D | 4
E | 1
D | 5
A | 5
C | 2


Result Set
==========
A - 5
B - 3
C - 2
D - 5
E - 1


Question
========
How to I retrieve the latest record based on "Ver" for
each "Item"?

Thanks.




__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/

--
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: simple query question

select Item, Ver
from MyTable m
where Ver = (select max(Ver) from MyTable where Item =
m.Item)
order by 1


--- ascll wrote:
> Greetings,
>
> Table Structure
> ===============
> Item - varchar
> Ver - int
>
>
> Table_A with Data
> =================
> Item | Ver
> ----------
> A | 1
> B | 1
> B | 2
> C | 1
> D | 1
> A | 3
> B | 3
> D | 2
> D | 3
> A | 4
> D | 4
> E | 1
> D | 5
> A | 5
> C | 2
>
>
> Result Set
> ==========
> A - 5
> B - 3
> C - 2
> D - 5
> E - 1
>
>
> Question
> ========
> How to I retrieve the latest record based on "Ver"
> for
> each "Item"?
>
> Thanks.
>
>
>
>
> __________________________________
> Celebrate Yahoo!'s 10th Birthday!
> Yahoo! Netrospective: 100 Moments of the Web
> http://birthday.yahoo.com/netrospective/
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
>
http://lists.mysql.com/win32?unsub=homam_sa@yahoo.com
>
>

--
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: simple query question

One way is to use 'group by'

SELECT MAX(ver) FROM Table_A GROUP BY Item

Sorry, too many beers to remember for sure, but I believe this is what you
are looking for.

Good luck.



----Original Message Follows----
From: ascll
To: win32@lists.mysql.com
Subject: simple query question
Date: Mon, 7 Mar 2005 22:38:31 -0800 (PST)
MIME-Version: 1.0
Received: from lists.mysql.com ([213.136.52.31]) by mc1-f15.hotmail.com with
Microsoft SMTPSVC(6.0.3790.211); Mon, 7 Mar 2005 22:39:00 -0800
Received: (qmail 806 invoked by uid 109); 8 Mar 2005 06:38:37 -0000
Received: (qmail 782 invoked from network); 8 Mar 2005 06:38:37 -0000
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jE+KZlt3OVuatQLtJKEb/HRrkiwMxRY76M=
Mailing-List: contact win32-help@lists.mysql.com; run by ezmlm
List-ID:
Precedence: bulk
List-Help:
List-Unsubscribe:

List-Post:
List-Archive: http://lists.mysql.com/win32/16721
Delivered-To: mailing list win32@lists.mysql.com
Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
b=Vf6HqUs2nIYjfZjDMo2CsqpOAlxGWSfQ4z71jUX3Q7bKu7hFBqL+8hy3bn 9e/VnvGsf1a9t2DYjLEc/okmiDjYG85zzKqaTezEWwwP28AqkCC5kGsj3Mdn YsxccKVi7ogXEAm5pI52FaEDx+5kNpLOPUr/JMMqDp6VbXBUy8A6Y=
;
X-Virus-Checked: Checked
Return-Path:
win32-return-16721-carlos_bittencourt=hotmail.com@lists.mysq l.com
X-OriginalArrivalTime: 08 Mar 2005 06:39:00.0597 (UTC)
FILETIME=[831B3250:01C523A9]

Greetings,

Table Structure
===============
Item - varchar
Ver - int


Table_A with Data
=================
Item | Ver
----------
A | 1
B | 1
B | 2
C | 1
D | 1
A | 3
B | 3
D | 2
D | 3
A | 4
D | 4
E | 1
D | 5
A | 5
C | 2


Result Set
==========
A - 5
B - 3
C - 2
D - 5
E - 1


Question
========
How to I retrieve the latest record based on "Ver" for
each "Item"?

Thanks.




__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=carlos_bittencourt@hotmai l.com



--
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: simple query question

------_=_NextPart_001_01C5D57D.30B96DBF
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Well, if you don't have rows in trainee_course, you can't retrieve
course info for a trainee. There's no way to make that kind of join
with the current table structure. There either must be existing rows in
trainee_course or trainee would have to include an FK to course.


Robert Nurse
STG
Contractor - Applications Development
Enterprise Management Center
Computational & Information Sciences Directorate

-----Original Message-----
From: ascll [mailto:ascll@yahoo.com ]
Sent: Thursday, October 20, 2005 4:17 AM
To: win32@lists.mysql.com
Subject: simple query question

Greetings,

## Table structure for 3 involved tables

CREATE TABLE IF NOT EXISTS `course` (
`Course_Code` smallint unsigned NOT NULL auto_increment,
`Course_ID` varchar(15) NOT NULL default '',
`Name` varchar(100) default NULL,
PRIMARY KEY (`Course_Code`),
UNIQUE KEY (`Course_ID`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

CREATE TABLE IF NOT EXISTS `trainee` (
`Trainee_Code` mediumint unsigned NOT NULL auto_increment,
`Trainee_ID` varchar(15) NOT NULL default '',
`Name` varchar(50) default NULL,
PRIMARY KEY (`Trainee_Code`),
UNIQUE KEY (`Trainee_ID`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;


### As intermediate table to store the course(s) that joined by
particular trainee

CREATE TABLE IF NOT EXISTS `trainee_course` (
`Trainee_Code` mediumint unsigned NOT NULL default '0',
`Course_Code` smallint unsigned NOT NULL default '0',
PRIMARY KEY (`Trainee_Code`,`Course_Code`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

#################################################


Question
========
How to I retrieve course info based on Course_Code for a PARTICULAR
trainee based on Trainee_Code, which do NOT EXIST in trainee_course ?

In order words, how do I get course info for a PARTICULAR trainee that
he or she has NOT joined (NO need to show joined course info) ?


Thanks and I'm using MySQL v4.1.14 :-)


=20
=20
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
=20
To unsubscribe:
http://lists.mysql.com/win32?unsub=3Drnurse@arl.army.mil
=20




------_=_NextPart_001_01C5D57D.30B96DBF--

RE: simple query question

This should give you a list of the courses the trainee is not in.

SELECT *
FROM course AS c=20
LEFT JOIN trainee_course AS tc ON c.Course_Code =3D tc.Course_Code=20
LEFT JOIN trainee AS t ON tc.Trainee_Code =3D t.Trainee_Code
WHERE t.Trainee_Code IS NULL

John B.

-----Original Message-----
From: ascll [mailto:ascll@yahoo.com]=20
Sent: Thursday, 20 October 2005 5:47 PM
To: win32@lists.mysql.com
Subject: simple query question

Greetings,

## Table structure for 3 involved tables

CREATE TABLE IF NOT EXISTS `course` (
`Course_Code` smallint unsigned NOT NULL
auto_increment,
`Course_ID` varchar(15) NOT NULL default '',
`Name` varchar(100) default NULL,
PRIMARY KEY (`Course_Code`),
UNIQUE KEY (`Course_ID`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

CREATE TABLE IF NOT EXISTS `trainee` (
`Trainee_Code` mediumint unsigned NOT NULL
auto_increment,
`Trainee_ID` varchar(15) NOT NULL default '',
`Name` varchar(50) default NULL,
PRIMARY KEY (`Trainee_Code`),
UNIQUE KEY (`Trainee_ID`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;


### As intermediate table to store the course(s) that
joined by particular trainee

CREATE TABLE IF NOT EXISTS `trainee_course` (
`Trainee_Code` mediumint unsigned NOT NULL default
'0',
`Course_Code` smallint unsigned NOT NULL default
'0',
PRIMARY KEY (`Trainee_Code`,`Course_Code`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

#################################################


Question
========
How to I retrieve course info based on Course_Code for
a PARTICULAR trainee based on Trainee_Code, which do
NOT EXIST in trainee_course ?

In order words, how do I get course info for a
PARTICULAR trainee that he or she has NOT joined (NO
need to show joined course info) ?


Thanks and I'm using MySQL v4.1.14 :-)


=09
=09
__________________________________=20
Yahoo! Mail - PC Magazine Editors' Choice 2005=20
http://mail.yahoo.com


--
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