Topic: [SOLVED] its new year and I am stuck!!!

[ToonMariner]

OK I have no life until this gets done so pretty please help me out!!!!

I have a table or items and I need to select all fields from the table but distinct values from just one field

eg.

many items and numerous occuencies of a category - I want the last entry from each category.

Any help much appreciated.... (I know I have done this before but just can't get it...)

[drranch]

Try this out...

Code: [Select]

SELECT * FROM tablename ORDER BY id DESC LIMIT 1
HA your not the only one with out a life. 

[ToonMariner]

sorry didn't make it clear...

There are many categories 1,2,3,4,5,6,7,8,9 etc etc and multiple entires in each category...

so there could be 20 in cat 3 and 17 in cat 8 what I need is the last entry from each category.

PS I also need to select data from a couple of other tables in the query too but when ever I use more than one field in the select part for the query and have a DISTINCT on one of them the query fails....

Sorry for not being clear.

[drranch]

What is your current syntax.  Reading syntax helps me to better understand what your looking for.

[ToonMariner]

Code: [Select]

SELECT
`media`.`media_id` ,
`media`.`user_id` ,
`media`.`media_key` ,
`media`.`type` ,
`media`.`title` ,
DISTINCT(`media`.`categories`) ,
`user`.`username` ,
`sections`.`url` ,
DATEDIFF(NOW(), `media`.`added`) AS `period`
FROM
`media` ,
`user` ,
`sections`
WHERE
`media`.`flag` != 'm'
AND
`user`.`user_id` = `media`.`user_id`
AND
`user`.`active` = 'y'
ORDER BY
`media`.`added` DESC


[Barand]

try

Code: [Select]

SELECT
`media`.`media_id` ,
`media`.`user_id` ,
`media`.`media_key` ,
`media`.`type` ,
`media`.`title` ,
`media`.`categories` ,
`user`.`username` ,
`sections`.`url` ,
DATEDIFF(NOW(), `media`.`added`) AS `period`
FROM
`media` ,
`user` ,
`sections`,
                                                            (SELECT categories, MAX(added) as latest
                                                                FROM media GROUP BY categories) as x
WHERE
`media`.`flag` != 'm'
AND
`user`.`user_id` = `media`.`user_id`
AND
`user`.`active` = 'y'
                                                  AND       
                                                             media.categories       =           x.categories
                                                  AND        media.added            =           x.latest
ORDER BY
`media`.`added` DESC

[ToonMariner]

Barand...

Thanks for the reply...
That query (I think) does the business but returns multiple duplicate entries.  I'd love a little precis on what the query is doing as I 'kind of' get what its doing but not totally...

ANyway and update...

I tried this...

Code: [Select]

SELECT
DISTINCT(`media`.`categories`) ,
`media`.* ,
`user`.`username` ,
`sections`.`url` ,
DATEDIFF(NOW(), `media`.`added`) AS `period`
FROM
`media` ,
`user` ,
`sections`
WHERE
`media`.`flag` != 'm'
AND
`user`.`user_id` = `media`.`user_id`
AND
`user`.`active` = 'y'
GROUP BY
`media`.`categories`
ORDER BY
`media`.`added` DESC


with some success (it appears the distinct coming first in the select clause is important).

How ever it selects the earliest records in the dataset rather than the latest.  Would anyone be so kind as to suggest why?

[Barand]

SELECT a, b, c, SUM(d) FROM table GROUP BY a

When using GROUP BY you should group by all selected columns that are not aggregated (ie not SUM(), COUNT() etc) and SQL normally enforces this. MySQL is lenient and lets you group by a single col as in the example above.

In this case the manual states that the values of b and c will be unpredictable but they usually are taken from the first record in each group.

+ + +

You can think of the subquery as a temporary table called x which contains the latest date for each category from the media table

             media                        subquery
             id    cat   date             cat     maxdate
             ----+-----+-------------     -----+-------------
              1     1    2007-01-01         
              2     1    2007-01-02         
              3     1    2007-01-03  <--->  1     2007-01-03
              4     2    2007-01-01
              5     2    2007-01-02          
              6     2    2007-01-04  <--->  2     2007-01-04

Joining to this should identify the latest record for each category

A thought just occured - if you have several recs on that last date it pulls them all. May have to use MAX(id) in the subquery and match on that instead of date.

[ToonMariner]

This is the query I ended up with (just a couple of minor alterations in field names etc) and it works like a charm.

Code: [Select]

SELECT
`media`.* ,
`user`.`username` ,
`sections`.`url` ,
`sections`.`string_key` ,
DATEDIFF(NOW(), `media`.`added`) AS `period`
FROM
`media` ,
`user` ,
`sections`,
                        (
SELECT
DISTINCT(`categories`) ,
MAX(`media_id`) as `latest`
FROM
`media`
GROUP BY
`categories`
) AS `x`
WHERE
`media`.`flag` != 'm'
AND
`user`.`user_id` = `media`.`user_id`
AND
`user`.`active` = 'y'
AND       
`media`.`categories`       =           `x`.`categories`
AND
`media`.`media_id`            =           `x`.`latest`
AND
`sections`.`section_id` = `media`.`categories`
ORDER BY
`media`.`added` DESC



MANY THANKS!!!!