[SOLVED] SQL Distinct

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

[SOLVED] SQL Distinct

Post by hairyjim »

Hi all,

Code: Select all

$sql = "SELECT DISTINCT product FROM downloads ORDER BY release_date";
The above returns my latest software release for a particular product.

Now I have never really gone beyond the SELECT title, date FROM etc type of SQL statments.

I have noticed that I (because I don't know how) cannot get to the other fields in the table. For instance, I tried to do this:

Code: Select all

$sql = "SELECT DISTINCT product, title, DATE_FORMAT(release_date,'%d/%m/%Y') as formatted_release_date FROM downloads ORDER BY release_date";
So I would get a list of the latest releases showing their title and release date. But obviously this does nto work.

Could someone please advise me on how to achieve this?
Last edited by hairyjim on Wed Jul 07, 2004 7:19 am, edited 1 time in total.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Code: Select all

$sql = "SELECT DISTINCT(product), title, DATE_FORMAT(release_date,'%d/%m/%Y') as formatted_release_date FROM downloads ORDER BY release_date";
that should work, what error does it come up with?
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post by hairyjim »

It selects everything in the db.

Code: Select all

SELECT DISTINCT(product), title, format, DATE_FORMAT(release_date,'%d/%m/%Y')
It does a DISTINCT on all the fields listed after the keyword.
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Perhaps....

Code: Select all

SELECT product, title, format, DATE_FORMAT(release_date,'%d/%m/%Y') GROUP BY product
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post by hairyjim »

I would have replied sooner but the wife has been keeping me busy.

Yeah I have tried the group by and this does not work for me.

It seems to still return the first release rather than the latest release.

This is driving me crazy, this should be simple.
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

You will need to add back your ORDER BY clause to the end of the SQL statement.
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post by hairyjim »

I just can't get this working at all :evil:

Code: Select all

$sql = "SELECT product, title, format, version, DATE_FORMAT(release_date,'%d/%m/%Y') as formatted_release_date FROM downloads GROUP BY product ORDER BY release_date desc";
Basically I have 5 columns. Product that contains prod1, prod2, prod3 & prod4. Format is Macintosh or Windows, version is the version number i.e 1.2.1, 2.2.3. Finally there is date, which is the release date of the software.

Now I have data like the below (for test purposes)

Product title format version date
prod1 Product1 Windows 1.1 01/01/04
prod1 Product1 Macintosh 1.1 01/02/04
prod2 Product2 Windows 2.5 10/05/04
prod2 Product2 Macintosh 2.5 10/05/04
prod3 Product3 Macintosh 1.6 02/03/04
prod4 Product4 Windows 2.2 15/03/04
prod1 Product1 Macintosh 1.2 01/07/04

You will notice I have a record at the very end which is a new release for prod1 on the windows platform. The only difference being is the version number.

When I do the above mentioned group by statement I do not get this new release showing.

Please please help me - its not nice seeing a grown man beg....but this has driven me crazy for several days now. :(

All I want to do is list the latest release of each product (windows & Mac)!

Don't get me started on the DISTINCY keyword, how useless is that if you cannot extract data from the other columns!
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

DISTINCT is useful, I use it on a regular basis. It's all too easy to think that something is useless when you don't fully understand.

Given your basic knowledge of SQL query constructs, I would suggest you add a further column to you database table (something like status) which could hold info like current, beta, archive etc... Then simply select all from the table where status is current and add your order by clause to order the results.

At present you don't have a column where group by will give you the results you are trying to achieve.
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

distinct is of no use to you here, distinct is rarely useful unless the db is designed wrong. What you actually need is a correlated subquery but because of the nature of your data you can get away with grouping and selecting the max of the release and releasedate columns... like this:

Code: Select all

select product, title, format, max(version), max(date)
  from downloads
group by product, title, format

with a correlated subquery:

select product, title, format, version, date
  from downloads
 where version = (select max(version)
                             from downloads as downloads2
                           where downloads2.product = downloads.product
                               and downloads2.title = downloads.title
                               and downloads2.format = downloads.format)
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post by hairyjim »

After much messing around I added another column for type release or archive.

Cheers for the help anyway chaps.

Jim
Post Reply