SELECT t1.* FROM (SELECT tag, category, COUNT(*) AS count FROM tags INNER JOIN stuff USING (id) GROUP BY tag, category) t1 LEFT OUTER JOIN (SELECT tag, category, COUNT(*) AS count FROM tags INNER JOIN stuff USING (id) GROUP BY tag, category) t2 ON (t1.tag = t2.tag AND (t1.count < t2.count OR t1.count = t2.count AND … Read more
There are various simpler and faster ways. 2x DISTINCT ON SELECT * FROM ( SELECT DISTINCT ON (name) name, week AS first_week, value AS first_val FROM tbl ORDER BY name, week ) f JOIN ( SELECT DISTINCT ON (name) name, week AS last_week, value AS last_val FROM tbl ORDER BY name, week DESC ) l … Read more
A standard approach is to use groupby(keys)[column].idxmax(). However, to select the desired rows using idxmax you need idxmax to return unique index values. One way to obtain a unique index is to call reset_index. Once you obtain the index values from groupby(keys)[column].idxmax() you can then select the entire row using df.loc: In [20]: df.loc[df.reset_index().groupby([‘F_Type’])[‘to_date’].idxmax()] Out[20]: … Read more
One way select t1.* from (select ForeignKeyId,AttributeName, max(Created) AS MaxCreated from YourTable group by ForeignKeyId,AttributeName) t2 join YourTable t1 on t2.ForeignKeyId = t1.ForeignKeyId and t2.AttributeName = t1.AttributeName and t2.MaxCreated = t1.Created See also Including an Aggregated Column’s Related Values for 5 different ways to do this kind of query
Use ROW_NUMBER() : SELECT s.id,s.tag,s.version FROM ( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.version DESC) as rnk FROM YourTable t) s WHERE s.rnk = 1
There are many alternatives to solves this, one which I recommend is to have joined a subquery which separately gets the latest ID (assuming that the column is AUTO_INCREMENTed) for each store_ID. SELECT a.* FROM tableName a INNER JOIN ( SELECT store_ID, MAX(ID) max_ID FROM tableName GROUP BY store_ID ) b ON a.store_ID = b.store_ID … Read more
Use: SELECT tbl.* FROM TableName tbl INNER JOIN ( SELECT Id, MIN(Point) MinPoint FROM TableName GROUP BY Id ) tbl1 ON tbl1.id = tbl.id WHERE tbl1.MinPoint = tbl.Point
To get latest record per customer among for each city based on created_at you can use a self join DB::table(‘yourTable as t’) ->select(‘t.*’) ->leftJoin(‘yourTable as t1’, function ($join) { $join->on(‘t.Customer’,’=’,’t1.Customer’) ->where(‘t.City’, ‘=’, ‘t1.City’) ->whereRaw(DB::raw(‘t.created_at < t1.created_at’)); }) ->whereNull(‘t1.id’) ->get(); In plain SQL it would be something like select t.* from yourTable t left join yourTable … Read more
To UNION the resulting rows of all three queries and then pick the 5 rows with the highest amount: (SELECT event_id, count(*) AS amount FROM pageview GROUP BY event_id ORDER BY pageviews DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) FROM upvote GROUP BY event_id ORDER BY upvotes DESC, rand() LIMIT 1000) UNION ALL … Read more
Assuming relatively few rows in options for many rows in records. Typically, you would have a look-up table options that is referenced from records.option_id, ideally with a foreign key constraint. If you don’t, I suggest to create one to enforce referential integrity: CREATE TABLE options ( option_id int PRIMARY KEY , option text UNIQUE NOT … Read more