Can i use Select Distinct?

  • Thread starter Thread starter hermanko
  • Start date Start date
H

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
 
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];
 
-----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,

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
Click to expand...
Click to expand...
 
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];

Jeff said:
Select FileCode, Name, Max(Version) as MaxVersion
Group By FileCode, Name
Click to expand...
Click to expand...


You have used ORDER BY when the suggested query used
GROUP BY
 
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
-----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-----


[email protected] said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL HELP 4
SELECT Statement Question 5
sql statement 1
Help with Distinct 4
Relateive maximum/ price value of a date 1
Counts number of entries per day in query 4
Alternative to group by 7
Create List of Dates that fall between 2 Dates 2

Back
Top