Use two sorts in query 1

[TrekBiker]

I want to sort this query first by the Category column then by descending Overall scores. It needs more than just sorting each column.

Any guidance will be much appreciated.

 

[combo]

In the query designer add sorting to the first field first, next for the second one.
ORDER BY sql clause help from MS here.

combo

 

[Andrzejek]

Yes... What's wrong with:

[tt]Select...
From...[BLUE]
ORDER BY Category, Overall DESC[/BLUE][/tt]
[PONDER]

Unless you want something else when you say: "It needs more than just sorting each column."

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Ah, nothing wrong with that apart from my mental aberration. Thanks both.

 

[Andrzejek]

Just a small addition to something that is even missing from the link combo provided.

If - in your Select statement - field [tt]Category[/tt] is a second field, and [tt]Overall[/tt] field is 7th (for example), you may do:[blue]
[tt]ORDER BY 2, 7 DESC[/tt][/blue]
but I would not advise this approach.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks Andy, will give that a miss then.

Related question with the table now looking like this

For each Category I'd now like to pick out the Company with the highest Overall figure, so

Best B2B Customer Experience, OnTheMarket, 11946
Best B2B Customer Experience - Professional Services, Etch, 10927
etc

Can this be done in another query?

 

[Andrzejek]

That should be another thread, but...
[tt]
Select Category, MAX(Overall) As MaxOverall
From TableName
Group By Category[/tt]

If you want to add [tt]Company[/tt] field to this query, you will need to provide additional information about your table, like a PK field.

Or you may just try this:

[pre]
Select A.Category, A.Company, A.Overall
From TableName A,
(Select Category, MAX(Overall) As MaxOverall
From TableName
Group By Category) X
Where A.Category = X.Category
And A.Overall = X.MaxOverall
Order by ...
[/pre]
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Yes, apologies, did intend to confess it should have been in a new thread.

Huge thanks for the response. I'd tried a query including Company and it didn't give the right answer, not surprisingly on reflection. I can cop out and get the Company name from another table but your solution looks neat. I'm not that au fait with subqueries so time to learn.