Could you tell me how to translate the following SQL code to Linq To SQL or Linq To Entites? The correst SQL code is: select CollectId,url,userid,pubtime from Collect group by url,collectid,userid,pubtime having pubtime >= (select max(pubtime) from collect d where d.url = collect.url ) order by Collect.pubtime desc Th...
I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value. Normally I would use a HAVING clause in my SQL to do this, but the Criteria API doesn't seem to support that at this moment. In raw ...
I have a MySQL query like this: SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q WHERE criterion="value" GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10; I want to restrict the results to rows where Occurrences>0. This seems very simple to me, but I can't seem to make it work. No matter what I tr...
I ultimately need a list of "import" records that include "album" records which only have one "song" each. This is what I'm using now: select i.id, i.created_at from imports i where i.id in ( select a.import_id from albums a inner join songs s on a.id = s.album_id group by a.id having 1 = count(s.id) ); The nested sele...
Here is my query: select word_id, count(sentence_id) from sentence_word group by word_id having count(sentence_id) > 100; The table sentenceword contains 3 fields, wordid, sentenceid and a primary key id. It has 350k+ rows. This query takes a whopping 85 seconds and I'm wondering (hoping, praying?) there is a faster way to find all...
I would like to GROUP BY an item in my table, which has an associated monetary value. This I can work out as a percentage, however there are often too many items in the list (too many to be useful, for example in a graph). Consequently, I would like to put those items that have a contribution to the total of less than say 5% into an 'Oth...
Hi, I am writing a select subquery that returns a COUNT. However, I need to use a HAVING clause in the subquery and as a result need to specify that column in my subquery SELECT. So the result is that I have 2 columns: COUNT and column_for_having_clause. Is there any way that i can tell SQL to take just the first column. I get an ...
I have a table programparticipants, I am currently successfully querying the IDs where count(name) > 1. What I need now is to query the names that belong to those IDs where count(name) > 1. Can anyone help me please? Example, data result currently being returned: ID count(name) 1 2 3 4 4 ...
When executing in *ONLY_FULL_GROUP_BY* mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query so I don't get the error anymore in *ONLY_FU...
So... It is possibile to use a WHERE clause after a HAVING clause? The first thing that comes to my mind is sub queries, but I'm not sure. P.S. If the answer is affirmative, could you give some examples? ...
I have a table like this: +-----+-----+-------+ | id | fk | value | +-----+-----+-------+ | 0 | 1 | peter | | 1 | 1 | josh | | 3 | 2 | marc | | ... | ... | ... | I'd like now to get all entries which have more than one value. The expected result would be: +-----+-------+ | fk | count | +-----+-------+ | 1 | 2 ...
I have a project which needs to use an agregrate function to sort and return relevant records from one of my active record models. Problem is, despite seeing it used on numerous rails tutorials, and despite it being in the online ActiveRecord documentation, my Rails app throws this error at me when the method is called: Unknown key(s):...
I'd like to only select the rows where the count is greater than 1 (in other words the duplicates) right now from a few thousand records i am mostly seeing ones with a few 2s and 3s here and there SELECT count( * ) AS `Number` , GI . * FROM `GeneralInformation` AS GI GROUP BY `FirstName` , `Surname` how can I do this? ...
Hi, how do I write this query in linq (vb.net)? select B.Name from Company B group by B.Name having COUNT(1) > 1 ...
Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to “check out” equipment. When he selects a product, I need to query which stock locations have the available Qty, and tell the user which location to walk to/ retrieve product. Here is a query for a particular [StockLocation_...
I'm trying to run the following query in SQLite 3: SELECT *, DISTANCE(latitude, longitude, ?, ?) AS "distance" FROM "country" WHERE "id" NOT LIKE ? HAVING "distance" <= ? ORDER BY "distance" ASC; But I get the following error: SQLSTATE[HY000]: General error: 1 a GROUP BY clause is required before HAVING I don't understand ...
what I need to test for on my table is if there are rows for a given user id and order id on two separate days (DATETIME field for a timestamp). I'm pretty sure I'd need a having clause and that's why I'm here...that frightens me terribly. ...
Hi, Does SQL Server 2005 support ANY or EVERY with HAVING? Suppose I have two tables Training(TrainingID, TrainingCloseDate) and TrainingDetail(TrainingDetailID,TrainingID,LKClassCode,CompletionDate). For one TrainingID, there can be multiple values in TrainingDetail with different LKClassCode. I need to find all the TrainingIDs which...
SELECT region, person, sum(dollars) as thousands FROM sales GROUP BY region, person ORDER BY region, sum(dollars) desc The SQL above produces a complete list of sales people per region like this region person thousands canada mike smith $114 canada joe blog $76 canada pete dodd $45 usa ...
Hi! Im trying to get this query to work but i get this error: Unknown column 'zips.city' in 'having clause' `$query = "SELECT zips.* FROM zips HAVING zips.city LIKE '%$city%' AND zips.stateabbr LIKE '%$state%' LIMIT 1"; $result = mysql_query($query) or die (mysql_error());` my zips table has a...