Grouping and Ordering the Groups

Complicated SQL (at least for me)

[web_young]

I have a table that holds data for projects. One field holds the company name and another holds the date the project was entered. Now when I display the projects I want them to be grouped by company which I use a Order By for. But I also want the newest projects at the top. I hope I'm explaining this clearly, but so far I've been able to have it one way or the other but not both. I can order by company name or by work order id. Here's a sample sql query that I'm using:

SELECT *
FROM mytables
ORDER BY wo_id DESC,
company_name,
proj_completed_status

Any help?

[phranque]

you didn't say what the column names are, but assuming the date column is "proj_completed_status" try this:

SELECT *
FROM mytables
ORDER BY proj_completed_status DESC,
company_name

[LifeinAsia]

It might help if you posted some sample data and the results you are trying to create.

[web_young]

Ok, so going back to my original example, but removing unnecessary stuff:

SELECT *
FROM mytables
ORDER BY wo_id DESC,
company_name

When I run the script like that I get:

Company A
1st project listing

Company B
1st project listing

Company C
1st project listing

Company A
2nd project listing

Company C
2nd project listing

Company B
2nd project listing

But what I want is:

Company A
1st project listing
2nd project listing

Company B
1st project listing
2nd project listing

Company C
1st project listing
2nd project listing

So I thought I could change the query to this:

SELECT *
FROM mytables
ORDER BY company_name,
wo_id DESC

Which works but I really need them listed in order of ID, so if company C gets a new project they are on top of the list like this:

Company C
3rd project listing (newest)
1st project listing
2nd project listing

Company A
project listings

Company B
project listings

Does that make sense?

[justageek]

Try this:

SELECT *
FROM mytables
GROUP BY company_name
ORDER BY wo_id DESC

JAG

[web_young]

That's getting closer, but now it only displays one (first/last?) project from each company:

Company C
project 1

Company A
project 1

Company B
project 1

[justageek]

Oops. I thought you just wanted the latest one for each. If you want them all then try this:

SELECT *
FROM mytables
ORDER BY company_name ASC, wo_id DESC

That should order the company names and then each work order in descending order for each.

JAG

[web_young]

Ok, even closer, that lists the projects in company groups, but what I'd like to see is if company C has the newest project they would be at the top of the list. So not necessarily alphabetical order for the companies but the company with the most recent would show up at the top of the list, like this:

Company C
project 1
project 2
project 3

Company A
project 1
project 2
project 3

Maybe there isn't a way to do this with SQL queries? What about with PHP?