Can i use Select Distinct?

[hermanko]

Hi,

Let's say I have the following table that contains historical data on
file info:

FileCode Name Version
01 Alpha 1
01 Alpha 2
01 Alpha 3
02 Bravo 1
03 Charlie 1
03 Charlie 2


I would like an SQL statement to select distinct FileCodes in a query
(i.e. 01, 02, 03), while selecting the most recent version of the Name,
and doesn't include version field, so that the resulting query looks
like this:

01 Alpha 3
02 Bravo 1
03 Charlie 2

I'm a beginner with SQL, so any help would be greatly appreciated!
Herman

 

[Jeff L]

Select FileCode, Name, Max(Version) as MaxVersion

From YourTableName

Group By FileCode, Name

 

[hermanko]

I have this, like you said, but it doesn't work. I get an error.

SELECT tblDocList.[File Code], tblDocList.[File Name],
Max(tblDocList.Version) AS MaxVersion
FROM tblDocList
ORDER BY tblDocList.[File Code], tblDocList.[File Name];

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT FileCode, [Name], Max([Version]) As Ver
FROM table_name As T
GROUP BY FileCode [Name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJra34echKqOuFEgEQIdCQCfZz7HwnZmYH330d8bvcggQOO8GYsAn2ks
mPitDVJcobUL7HvuxMu4t3nU
=fmv8
-----END PGP SIGNATURE-----

 

[hermanko]

Hi,

Your sql seems identical to the previous reply. However, I realized
that my Name fields are all unique. i.e. the file names should be
Alpha1, Alpha2, Alpha3, etc, so that the fields are unique.

How would this change the sql?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT FileCode, [Name], Max([Version]) As Ver
FROM table_name As T
GROUP BY FileCode [Name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJra34echKqOuFEgEQIdCQCfZz7HwnZmYH330d8bvcggQOO8GYsAn2ks
mPitDVJcobUL7HvuxMu4t3nU
=fmv8
-----END PGP SIGNATURE-----

Hi,

Let's say I have the following table that contains historical data on
file info:

FileCode Name Version
01 Alpha 1
01 Alpha 2
01 Alpha 3
02 Bravo 1
03 Charlie 1
03 Charlie 2


I would like an SQL statement to select distinct FileCodes in a query
(i.e. 01, 02, 03), while selecting the most recent version of the Name,
and doesn't include version field, so that the resulting query looks
like this:

01 Alpha 3
02 Bravo 1
03 Charlie 2

I'm a beginner with SQL, so any help would be greatly appreciated!
Herman

 

[Marshall Barton]

I have this, like you said, but it doesn't work. I get an error.

SELECT tblDocList.[File Code], tblDocList.[File Name],
Max(tblDocList.Version) AS MaxVersion
FROM tblDocList
ORDER BY tblDocList.[File Code], tblDocList.[File Name];

Select FileCode, Name, Max(Version) as MaxVersion
Group By FileCode, Name

You have used ORDER BY when the suggested query used
GROUP BY

 

[hermanko]

I.E. i would like a resulting query to display (no Version):

Filecode Name
01 Alpha3
02 Bravo1
03 Charlie2

Hi,

Your sql seems identical to the previous reply. However, I realized
that my Name fields are all unique. i.e. the file names should be
Alpha1, Alpha2, Alpha3, etc, so that the fields are unique.

How would this change the sql?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT FileCode, [Name], Max([Version]) As Ver
FROM table_name As T
GROUP BY FileCode [Name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJra34echKqOuFEgEQIdCQCfZz7HwnZmYH330d8bvcggQOO8GYsAn2ks
mPitDVJcobUL7HvuxMu4t3nU
=fmv8
-----END PGP SIGNATURE-----

Hi,

Let's say I have the following table that contains historical data on
file info:

FileCode Name Version
01 Alpha 1
01 Alpha 2
01 Alpha 3
02 Bravo 1
03 Charlie 1
03 Charlie 2


I would like an SQL statement to select distinct FileCodes in a query
(i.e. 01, 02, 03), while selecting the most recent version of the Name,
and doesn't include version field, so that the resulting query looks
like this:

01 Alpha 3
02 Bravo 1
03 Charlie 2

I'm a beginner with SQL, so any help would be greatly appreciated!
Herman

 

[hermanko]

Thanks for that correction...I've switched it to Group By, but it still
doesn't give me what I want....

now my sql is:

SELECT tblDocList.[File Code], tblDocList.[File Name],
Max(tblDocList.Version) AS MaxOfVersion
FROM tblDocList
GROUP BY tblDocList.[File Code], tblDocList.[File Name];

1) i would not like to show the Version field in the results
2) the sql still returns all values and not just the max....

ARgh!
Herman

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I had a feeling that would be the case. Use this instead:

SELECT FileCode, [Name], [Version]
FROM table_name As T
WHERE [Version] = (SELECT MAX([Version]) FROM table_name
WHERE FileCode = T.FileCode)

This assumes that the same FileCode value always goes w/ the Name's non
numeric value. E.g.:

FileCode Name Version
======== ==== =======
01 Alpha1 1
01 Alpha2 2
02 Bravo1 1
02 Bravo2 2
03 Charlie1 1

If it is something else, you will have to show us an example of the real
data.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJrpVYechKqOuFEgEQLgjACgrrnqPQFuR4uFTcBqbw496rusSskAoJ69
APZLlRdgvJOuvGJ6e1kbcWox
=eVaX
-----END PGP SIGNATURE-----

Hi,

Your sql seems identical to the previous reply. However, I realized
that my Name fields are all unique. i.e. the file names should be
Alpha1, Alpha2, Alpha3, etc, so that the fields are unique.

How would this change the sql?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT FileCode, [Name], Max([Version]) As Ver
FROM table_name As T
GROUP BY FileCode [Name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJra34echKqOuFEgEQIdCQCfZz7HwnZmYH330d8bvcggQOO8GYsAn2ks
mPitDVJcobUL7HvuxMu4t3nU
=fmv8
-----END PGP SIGNATURE-----

Hi,

Let's say I have the following table that contains historical data on
file info:

FileCode Name Version
01 Alpha 1
01 Alpha 2
01 Alpha 3
02 Bravo 1
03 Charlie 1
03 Charlie 2


I would like an SQL statement to select distinct FileCodes in a query
(i.e. 01, 02, 03), while selecting the most recent version of the Name,
and doesn't include version field, so that the resulting query looks
like this:

01 Alpha 3
02 Bravo 1
03 Charlie 2

I'm a beginner with SQL, so any help would be greatly appreciated!
Herman

 

[hermanko]

MGFoster:

I knew it had to do with a nest SELECT statement but i just don't have
enough sql experience to even begin writing up what you just did. It
nows seems to work!

Thanks everyone for bearing with me....
Herman

 

[Michel Walsh]

Hi,


Remove the unwanted fields and in the SELECT clause and in the GROUP BY
clause (probably [File Name] ).


Hoping it may help,
Vanderghast, Access MVP