HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


SQL Query Sort Help

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#74948 (In Topic #15568)
Avatar

Well-settled

Advanced Sorting Issue

I have a table that has a large amount of logged errors in it.

Each customer has an ID#. Sometimes that customer will have multiple errors, therefore the ID# will show up multiple times.

I can get the ID number (alphanumeric) to sort by Ascending or Descending. Great.

ID DATETIME ERRORMSG

But now I'm trying to do more advanced sorting…

I want customers who have the MOST errors to show up on the top of the list. So if Cus1 has 6 errors, Cus2 has 3 errors, Cus3 has 8 errors.. Cus3 will be on top, Cus1 comes next, Cus 2 comes after him.

How can I do this with an SQL query?
Back to the top
 
Posted
Rating:
#75164
Avatar

Community saint

I don't know how to do it in one query but I would do it by starting with this query

SELECT id, COUNT(id) AS total FROM ERRORTABLE Group By id Order By total DESC

Then cycle through that result with php and query for the records I needed.  I am sure not the most efficient method but I am no MySQL Guru so I would have to take whats probably the long way around. If no one else has better suggestions maybe that will help you?
Back to the top
 
Posted
Rating:
#75168
Avatar

Well-settled

I ended up using ORDER BY DATE DESC, ORDER BY CurrentID DESC;

And I just have the listview sorting by the one that has the most.

Thx for your help though.


Last edit: by Xarex
Back to the top
 
Posted
Rating:
#75176
Avatar

Community saint

I'm a little lost? I don't see how starting your sort by date will lump the id's together? And it certainly won't give you a count of who has most. I must go try this to see the results I wouldn't have approached it by date first?

See with Query I suggested  assuming you have 4 entries with ID 1 and 6 with ID 2 and 5 With ID 3 you'll get a result that looks like this

_________
|ID |Total|
| 2 | 6    |
| 3 | 5    |
| 1 | 4    |

So now all you need is While $result = fetchrow

Select id, datetime, errormsg, From errortable where id = $result[id];

Follow?

Of course I haven't written the query and coding logic out full cause I haven't looked to see how ocPortal implements this but I figure you know enough of what you're doing to know what I mean.
Back to the top
 
Posted
Rating:
#75178
Avatar

Well-settled

I apologize for the lack of explanation Duck.

This is not for ocPortal at all. I thought that was what General Chat meant.. up for discussion about anything.

I am actually using VB6 with DAO and I was working on a project at work.

We have a server program that receives log data from all of our clients that use our programs. These logs are sent to the server so we know what errors there are, what programs they come from, so they can be looked into and fixed.

But we don't have any program that actually reviews it aside from accessing the database itself. So my boss asked me to make a program to receive this and put it in order by DATE and by CustomerID in descending order.

But the customers with the most errors will show up. I misunderstood what he wanted. He doesn't want customer A to be shown 3 times. He wanted customer A to be shown once and a counter to show that he has 3 errors.

So I am using that SQL query, but once I retrieve the information I need, it is then sorted as it gets counted through a collection. Afterwards, it's added to a Listview where it is sorted by date.

So the customer is actually only showing up once.
Back to the top
 
Posted
Rating:
#75187
Avatar

Community saint

Ahh I see rather presumtious of me to assume you were using php and ocPortal my bad lol.

Anyway in that case then the first Query I gave you is exactly what you need indeed to show the customer and his totals and sorted by the most. You just unfortunately don't get the full list of his errors until you do another query.

I had a couple ideas for a subquery in a query that I thought might work but I hit some brick walls however I could do it via temp tables I imagine whereby you build a temptable off one set of results adding an extra sortable id to it and requery that table to get the result you want.  Used a method like that before to do something similar to what you asked and it worked out ok in my particular case.
Back to the top
 
Posted
Rating:
#75188
Avatar

Well-settled

I appreciate your efforts. I sure hope you didn't work too hard. I certainly was in dire need when I posted.. running out of ideas, or hoping there was some easy query to get me through.

But the code wasn't too hard. Just had to add a counter each time. And display the errors.

Boss changes his mind everyday with what he wants, but I eventually get there.
Back to the top
 
1 guests and 0 members have just viewed this: None
Control functions:

Quick reply   Contract

Your name:
Your message: