Page 1 of 1

[SOLVED] SQL Distinct

Posted: Fri Jul 02, 2004 5:46 am
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?

Posted: Fri Jul 02, 2004 10:11 am
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?

Posted: Fri Jul 02, 2004 10:27 am
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.

Posted: Fri Jul 02, 2004 10:53 am
by redmonkey
Perhaps....

Code: Select all

SELECT product, title, format, DATE_FORMAT(release_date,'%d/%m/%Y') GROUP BY product

Posted: Tue Jul 06, 2004 4:21 am
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.

Posted: Tue Jul 06, 2004 5:35 am
by redmonkey
You will need to add back your ORDER BY clause to the end of the SQL statement.

Posted: Tue Jul 06, 2004 10:43 am
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!

Posted: Tue Jul 06, 2004 2:47 pm
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.

Posted: Tue Jul 06, 2004 4:31 pm
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)

Posted: Wed Jul 07, 2004 7:04 am
by hairyjim
After much messing around I added another column for type release or archive.

Cheers for the help anyway chaps.

Jim