Can i use Select Distinct?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hermanko@gmail.com - 22 Jun 2006 17:24 GMT

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

Reply to this Message

Jeff L - 22 Jun 2006 18:21 GMT

Select FileCode, Name, Max(Version) as MaxVersion

>From YourTableName

Group By FileCode, Name

Reply to this Message

hermanko@gmail.com - 22 Jun 2006 18:43 GMT

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
> >From YourTableName
> Group By FileCode, Name

Reply to this Message

Marshall Barton - 22 Jun 2006 19:47 GMT

>I have this, like you said, but it doesn't work. I get an error.
>
[quoted text clipped - 6 lines]
>> >From YourTableName
>> Group By FileCode, Name

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

MGFoster - 22 Jun 2006 19:00 GMT

Try this:

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

Signature

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

> Hi,
>
[quoted text clipped - 20 lines]
> I'm a beginner with SQL, so any help would be greatly appreciated!
> Herman

Reply to this Message

hermanko@gmail.com - 22 Jun 2006 19:11 GMT

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
[quoted text clipped - 42 lines]
> > I'm a beginner with SQL, so any help would be greatly appreciated!
> > Herman

Reply to this Message

hermanko@gmail.com - 22 Jun 2006 19:48 GMT

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

Filecode Name
01 Alpha3
02 Bravo1
03 Charlie2

> Hi,
>
[quoted text clipped - 50 lines]
> > > I'm a beginner with SQL, so any help would be greatly appreciated!
> > > Herman

Reply to this Message

hermanko@gmail.com - 22 Jun 2006 19:51 GMT

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

Reply to this Message

Michel Walsh - 01 Jul 2006 12:25 GMT

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

> Thanks for that correction...I've switched it to Group By, but it still
> doesn't give me what I want....
[quoted text clipped - 11 lines]
> ARgh!
> Herman

Reply to this Message

MGFoster - 22 Jun 2006 20:02 GMT

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.

Signature

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

> Hi,
>
[quoted text clipped - 50 lines]
>>>I'm a beginner with SQL, so any help would be greatly appreciated!
>>>Herman

Reply to this Message

hermanko@gmail.com - 22 Jun 2006 20:13 GMT

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

Reply to this Message