Sorting by date before group by

[richtestani]

I am trying to sort my records by date, with the most current date first, then group it by a criteria. Problem I am having is I can group by [column] but it will only show the first records of each group which are the earlier dated records.


[tt]SELECT areas.id AS arid, area, articles.id AS aid, articles.article_title, articles.postdate, articles.areaid FROM articles LEFT JOIN areas ON articles.areaid=areas.id WHERE postdate <= "2004-10-10" GROUP BY arid;[/tt]

Thanks
Rich

 

[cLFlaVA]

Have you tried this?

[tt]SELECT
areas.id AS arid,
areas.area AS area,
articles.id AS aid,
articles.article_title AS title,
articles.postdate AS postdate,
articles.areaid as areaid
FROM
articles LEFT JOIN areas
ON articles.areaid=areas.id
WHERE
postdate <= "2004-10-10"
GROUP BY
arid
ORDER BY
articles.postdate DESC;[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[richtestani]

Yes, all it will do is order the grouped set. Which are still the earlier records in the table.

Thanks
Rich

 

[cLFlaVA]

What? Could you elaborate more?

I'm pretty sure that if you're using a GROUP BY clause without also utilizing any AGGREGATE FUNCTIONS, you won't get the results you think you'll get.

What result are you trying to achieve?

A typical use of the GROUP BY clause is as follows:
[tt]
|item |qty |
-------------------
|apple | 1 |
|orange | 2 |
|banana | 3 |
|apple | 4 |
|apple | 5 |
|orange | 6 |
-------------------

SELECT
item AS item,
SUM(qty) AS total
FROM
table_name
GROUP BY
item
ORDER BY
item
[/tt]

Will produce the following results:

[tt]
|item |total |
-------------------
|apple | 10 |
|banana | 3 |
|orange | 8 |
-------------------
[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[richtestani]

Yes, but I'm looking to get it sorted by date. Not a count.
It's being used to retreive the latest article in each of 5 areas.

So I do a simple JOIN, but GROUP BY areas

This groups it propery, but returns only the first record in each group. ORDER BY will only sort on the found set.

 

[ericbrunson]

Try: GROUP BY area HAVING max( postdate )

 

[richtestani]

I probably explained it wrong womewhere.

Take a look at this site

http://www.grindhousedesign.com

You'll see int he center of the page are 5 catagories:
Clubs, Bars....

Each one has an article related to them.

The articles listed there are the first ones posted under that catagory. I need to post the latest version. Here is the SQL I use to achieve the current.

SELECT DISTINCT(areaid), 
	areas.id, 
	articles.id,
	areas.area AS areaname,
	articles.article_title
	FROM articles, 
	areas 
	WHERE articles.areaid=areas.id 
	AND articles. postdate <= '".$mySQLdate."'
	GROUP BY areas.area
	ORDER BY areas.id, postdate;

So, it's almost what I want, but the problem is this will never update when new articles are posted.

Hoep this explains it a little more.
Thanks
Rich

 

[richtestani]

Whoops,
this is the code for the display.

SELECT DISTINCT(areaid), 
	areas.id, 
	articles.id,
	areas.area AS areaname,
	articles.article_title,
	articles.overview,
	articles.emailaddress,
	articles.author,
	articles.postdate
	FROM articles, 
	areas 
	WHERE articles.areaid=areas.id 
	AND articles.postdate <= '".$mySQLdate."'
	GROUP BY areas.area
	ORDER BY areas.id, articles.postdate