Hi all, I used to post the odd question here some years ago and I am hoping you guys are as helpful as you used to be as I am kind of out of my depth with a little SQL that goes something like this ...
I have a table containing data like this JobCreatedBy Client RunID Jane ClientA 546987 Jane ClientB 546987 Dave ClientA 546987 Jane ClientA 546987 Dave ClientC 546987 Jane ClientC 546987 Jane ClientA 546987 Dave ClientB 546987 Zack ClientA 546987
And I have a SELECT statement like this sSQL1 = "SELECT JobCreatedBy, Client FROM Tablename WHERE" sSQL1 = sSQL1 & " [RunID] = " & RunID sSQL1 = sSQL1 & " ORDER BY JobCreatedBy DESC"
This gets me a recordset with all the records for Jane grouped together and all records for Dave grouped together. What I need to be able to do is, if Jane has the most records matching my WHERE clause she should be the first record in the recordset, if its Dave then vice versa.
I thought I could do this by including something like 'COUNT(*) AS myTotal' in the select and having a Group By clause and ORDER BY myTotal, but it allways fails with 'Column Tablename.JobCreatedBy' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause'.
I dont understand this as there is a 'Group By' clause which includes that column. Anyways, whether this is the right way to go about it or not I could do with some help if anyone can point me in the right direction.
SELECT JobCreatedBy, Client, COUNT(*) As myTotal FROM
TableName WHERE [RUNID] = ....
Group By JobCreatedBy, Client
Order By COUNT(*) DESC, JobCreatedBy DESC
I now have a number of related problems 1/ I am feeling particularly dumb because I thought I had already tried the code in ruirib's suggestion and got an error, however I have tried it again and it is pretty much OK now (see next item) 2/ The code is still not ORDERing by the COUNT, which seems to be because the COUNT,which I have alaised to myCount,is returning a zero length string, even though there are some 40 records in the recordset thats returned. 3 when I run the sql in Enterprise Manager I get a value in myCount, however I am not sure I understand it,probably because I asked the wrong question. The count needs to be the Total records recorded for Jane,not the total record recorded for Jane for ClientA. Is that poss?
Topic Locked
Posted - 13 December 2004 : 14:09:50
