天道酬勤,学无止境

groupwise-maximum

Query to rank rows in groups

I'm using Apache Derby 10.10. I have a list of participants and would like to calculate their rank in their country, like this: | Country | Participant | Points | country_rank | |----------------|---------------------|--------|--------------| | Australia | Bridget Ciriac | 1 | 1 | | Australia | Austin Bjorklun | 4 | 2 | | Australia | Carrol Motto | 7 | 3 | | Australia | Valeria Seligma | 8 | 4 | | Australia | Desmond Miyamot | 27 | 5 | | Australia | Maryjane Digma | 33 | 6 | | Australia | Kena Elmendor | 38 | 7 | | Australia | Emmie Hicke | 39 | 8 | | Australia | Kaitlyn Mund | 50 | 9 | |

2022-03-10 13:32:42    分类:问答    sql   derby   rank   row-number   groupwise-maximum

希望创建一个只显示一列 MYSQL 的子查询(Looking to create a subquery that displays only one column MYSQL)

问题 该数据库用于图书借阅系统。 如果学生拿到一本书的副本,系统会接受那里的学生编号,并为他们提供该书的归还日期。 这是我正在使用的两个表 贷款表: CREATE TABLE loan ( `code` INT NOT NULL, `no` INT NOT NULL, taken DATE NOT NULL, due DATE NOT NULL, `return` DATE NULL, CONSTRAINT pri_loan PRIMARY KEY (taken), CONSTRAINT for_loan FOREIGN KEY (`code`) REFERENCES copy (`code`), FOREIGN KEY (`no`) REFERENCES student (`no`)); 学生表: CREATE TABLE student ( `no` INT NOT NULL, `name` VARCHAR(30) NOT NULL, school CHAR(3) NOT NULL, embargo BIT NOT NULL, CONSTRAINT pri_student PRIMARY KEY (`no`)); 首先,我希望计算图书副本的最晚到期日(它将显示该书的代码以及该书的最晚到期日),这是我使用此代码完成的,该代码运行良好 SELECT `code`, max(due)

2021-10-23 23:47:46    分类:技术分享    mysql   subquery   groupwise-maximum

Looking to create a subquery that displays only one column MYSQL

This database is for a book loan system. Where if a student takes a copy of a book the system accepts there student number and gives them a return date for the book. These are the two tables I am using loan table: CREATE TABLE loan ( `code` INT NOT NULL, `no` INT NOT NULL, taken DATE NOT NULL, due DATE NOT NULL, `return` DATE NULL, CONSTRAINT pri_loan PRIMARY KEY (taken), CONSTRAINT for_loan FOREIGN KEY (`code`) REFERENCES copy (`code`), FOREIGN KEY (`no`) REFERENCES student (`no`)); student table: CREATE TABLE student ( `no` INT NOT NULL, `name` VARCHAR(30) NOT NULL, school CHAR(3) NOT NULL

2021-10-22 13:32:31    分类:问答    mysql   subquery   groupwise-maximum

How can I include third column in a SELECT with MIN

In this query, I also want to SELECT a third column, wp_postmeta.post_id, in the same query. I can't figure out how to do this without appending it via a JOIN which involves subquerying. Isn't there a simpler easier way, without the use of subqueries? (Willing to concede on subqueries if the solution is efficient and not too resource-intensive.) MariaDB 10.1 create table wp_posts ( ID integer primary key auto_increment, post_title varchar(30), post_type varchar(30) ); ✓ create table wp_postmeta ( ID integer primary key auto_increment, post_id integer, meta_key varchar(30) not null default '

2021-10-21 09:31:55    分类:问答    sql   syntax   mariadb   groupwise-maximum

Get the latest records per Group By SQL

I have the following table: ----------------------------------------------------------- ID oDate oName oItem oQty oRemarks ----------------------------------------------------------- 1 2016-01-01 A 001 2 2 2016-01-01 A 002 1 test 3 2016-01-01 B 001 3 4 2016-01-02 B 001 2 5 2016-01-02 C 001 2 6 2016-01-03 B 002 1 7 2016-01-03 B 001 4 ff. I want to get the latest record for each name. So the result should be like this: ----------------------------------------------------------- oDate oName oItem oQty oRemarks ----------------------------------------------------------- 2016-01-01 A 001 2 2016-01

2021-08-02 04:36:52    分类:问答    sql   sql-server   select   sql-server-2008-r2   groupwise-maximum

find maximum of set of columns for multiple rows in mysql query

I have a table with the following fields and data. RefNo Year Month Code 1 2010 7 A 2 2009 10 B 3 2010 8 A 4 2010 5 B From this i've to find out the maximum(year, month) for a particular code. The result must be 3 2010 8 A 1 2010 7 B Please help me solve this.

2021-07-03 14:58:11    分类:问答    mysql   sql   groupwise-maximum

按组查找最大跑步次数(Finding running maximum by group)

问题 我需要使用R按组查找变量的运行最大值。使用df[order(df$group, df$time),]在组中按时间对变量进行排序。 我的变量具有一些NA,但我可以通过在计算中将它们替换为零来处理它。 这是数据帧df的样子: (df <- structure(list(var = c(5L, 2L, 3L, 4L, 0L, 3L, 6L, 4L, 8L, 4L), group = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L), .Label = c("a", "b"), class = "factor"), time = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L)), .Names = c("var", "group","time"), class = "data.frame", row.names = c(NA, -10L))) # var group time # 1 5 a 1 # 2 2 a 2 # 3 3 a 3 # 4 4 a 4 # 5 0 a 5 # 6 3 b 1 # 7 6 b 2 # 8 4 b 3 # 9 8 b 4 # 10 4 b 5 我想要一个变量curMax为: var | group | time | curMax 5 a 1 5 2 a 2 5 3

2021-04-14 08:51:53    分类:技术分享    r   max   groupwise-maximum

优化分组最大查询(Optimize groupwise maximum query)

问题 select * from records where id in ( select max(id) from records group by option_id ) 该查询即使在数百万行上也可以正常工作。 但是,正如您从explain语句的结果中看到的: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=30218.84..31781.62 rows=620158 width=44) (actual time=1439.251..1443.458 rows=1057 loops=1) -> HashAggregate (cost=30218.41..30220.41 rows=200 width=4) (actual time=1439.203..1439.503 rows=1057 loops=1) -> HashAggregate (cost=30196.72..30206.36 rows=964 width=8) (actual time=1438.523..1438.807

2021-04-10 04:42:34    分类:技术分享    sql   postgresql   query-optimization   greatest-n-per-group   groupwise-maximum

LEFT JOIN仅第一行(LEFT JOIN only first row)

问题 我读了许多关于仅获得左联接的第一行的主题,但是由于某种原因,这对我不起作用。 这是我的结构(当然是简化的) 提要 id | title | content ---------------------- 1 | Feed 1 | ... 艺人 artist_id | artist_name ----------------------- 1 | Artist 1 2 | Artist 2 feeds_artists rel_id | artist_id | feed_id ---------------------------- 1 | 1 | 1 2 | 2 | 1 ... 现在,我想获取文章并仅加入第一位艺术家,我想到了这样的事情: SELECT * FROM feeds LEFT JOIN feeds_artists ON wp_feeds.id = ( SELECT feeds_artists.feed_id FROM feeds_artists WHERE feeds_artists.feed_id = feeds.id LIMIT 1 ) WHERE feeds.id = '13815' 只是只获取feeds_artists的第一行,但已经行不通了。 由于数据库的原因,我无法使用TOP ,并且无法按feeds_artists.artist_id对结果进行feeds

2021-04-06 16:16:59    分类:技术分享    mysql   join   left-join   groupwise-maximum