sql how to select the next to last row of the groups with two or more rows
select last row from group by
how to select single row from multiple records in sql
sql partition by
sql server select last row in group
sql: selecting rows where column value changed from previous row
select last row sql
sql last record of a group
I have a table of performed actions over different object instances that have different versions. If I group the actions per instance and version, with this SELECT (abbreviated)
SELECT instance, version, COUNT(id) AS cnt FROM actions WHERE status=0 AND version IS NOT NULL GROUP BY instance, version
I obtain this table (abbreviated)
instance | version | cnt ----------+---------+------ 1021 | 18.1 | 263 1021 | 18.2 | 422 1021 | 19.1 | 949 1191 | 18.2 | 28 1195 | 18.1 | 584 1195 | 18.2 | 176 1195 | 18.3 | 437 1195 | 19.1 | 152 1195 | 19.2 | 545 1195 | 19.3 | 399 1196 | 18.3 | 844 1196 | 19.1 | 800 1197 | 18.3 | 2 1201 | 18.1 | 471 1201 | 18.2 | 584 1201 | 18.3 | 553 1201 | 19.1 | 498 1201 | 19.2 | 203 1201 | 19.3 | 36 1208 | 18.1 | 444 1208 | 18.2 | 548 1208 | 18.3 | 31 1208 | 19.2 | 357 1210 | 19.1 | 514 1211 | 18.2 | 341 1211 | 19.1 | 531 ....
now, I want the row corresponding to the previous to the last version for the instances that have more than one version.
So, in the example, I need to select the rows
instance | version | cnt ----------+---------+------ 1021 | 18.2 | 422 1195 | 19.2 | 545 1196 | 18.3 | 844 1201 | 19.2 | 203 1208 | 18.3 | 31 1211 | 18.2 | 341 ...
I have tried
GROUP BY instance HAVING count(*) >= 2 to begin by filtering the results, but it counts the original rows, not the resulting rows after the first
GROUP BY instance, version.
Any hint on how to achieve this?
Assuming that abbreviated results are stored in temp table test. Following query will give you the expected output.
select * from test where (instance,version)in (select instance,max(version) as version from test A where exists (select max(version) as version from test B where A.instance=B.instance and A.version<B.version group by instance) group by instance)
instance version cnt 1021 18.2 422 1195 19.2 545 1196 18.3 844 1201 19.2 203 1208 18.3 31 1211 18.2 341
Last row per group, With the introduction of windowed functions in SQL Server, there are a number The other is selective, containing about 33 000 partitions with only three rows each. BY (SELECT NULL))/10000, ROW_NUMBER() OVER (ORDER BY This window only spans two rows – the current row and the next one. The SQL ROW_NUMBER Function allows you to assign the rank number to each record present in a partition. In this example, we show you how to Select First Row from each SQL Group. The following SQL Query will. First, partition the data by Occupation and assign the rank number using the yearly income. Next, ROW_NUMBER is going to select the First
It seems you need (no optimization!)
WITH cte1 AS ( SELECT instance, version, COUNT(id) AS cnt FROM actions WHERE status=0 AND version IS NOT NULL GROUP BY instance, version ), cte2 AS ( SELECT instance, MAX(version) version FROM cte1 GROUP BY instance ), cte3 AS ( SELECT instance, MAX(version) version FROM cte1 LEFT JOIN cte2 USING (instance, version) WHERE cte2.instance IS NULL GROUP BY instance ) SELECT cte1.* FROM cte1 JOIN cte3 USING (instance, version)
Using OFFSET and FETCH with the ORDER BY , The starting row to return is determined by the OFFSET value and the To learn more about TOP, read the article Getting Started with SQL Server: 2. SELECT columns FROM table ORDER BY columns OFFSET rows-to-skip ROWS Employee ORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY. select * from employees For each employee, find all less earning people with the same role – here we need to perform two actions: 1) left join the table with itself using the role field. 2) add a condition to make sure the salary is the highest.
You can use window functions:
SELECT iv.* FROM (SELECT instance, version, COUNT(id) AS cnt, ROW_NUMBER() OVER (PARTITION BY instance ORDER BY version DESC) as seqnum FROM actions WHERE status = 0 AND version IS NOT NULL GROUP BY instance, version ) iv WHERE seqnum = 2;
An Essential Guide to MySQL ROLLUP By Practical Examples, This tutorial shows you how to use the MySQL ROLLUP() to generate multiple SELECT productline, SUM(orderValue) totalOrderValue FROM sales GROUP BY If you want to generate two or more grouping sets together in one query, you to generate the total order values by product lines and also the grand total row. SELECT TOP 1 * FROM table ORDER BY Id DESC; Also, this will work on SQL Server. I think that MySQL you might need to use: SELECT * FROM table ORDER BY Id DESC LIMIT 1 But, I'm not 100% sure about this. EDIT. Looking at the other answers, I'm now 100% confident that I'm correct with the MySQL statement :o) EDIT. Just seen your latest comment.
10 SQL tricks that you didn't think were possible, Last minute slide added to my ridiculously fast paced SQL talk at @JAXconf, the Should the two tables be joined in a nested loop or with a hashmap? WITH RECURSIVE t(v) AS ( SELECT 1 -- Seed Row UNION ALL SELECT v + It can produce one or several rows on which we will recurse afterwards. Next, we’ll need to join them so that a logon row is paired up with its logoff row. To match up a row with its previous row, we can enumerate the rows of the UserActivity table and include that enumeration in the two derived tables (rows in LogOn will all have odd row numbers and rows in LogOff will all have even row numbers).
[PDF] Advanced Programming Techniques with PROC SQL, single logical row of data spans two or more lines of output. is used to identify select similar sounding names including spelling variations. The next example illustrates the process of creating an SQL view. LAST.column in by-groups, and is able to identify the BETWEEN.column rows too in SAS-SQL, and leading� yea i was just giving method you can do it simply, if you have maintained ID for each row (primary key) it will work more and you will have more functions in hand. In the way you need its long code and also , it will be harder for you to use anything else over it in future. – devilcrab Apr 12 '13 at 11:10
SQL for Pattern Matching, The ability to recognize patterns found across multiple rows is important for lets you choose between showing one row per match and all rows per match. PARTITION BY divides the data from the Ticker table into logical groups LAST ROW : resume pattern matching at the next row after the last row of the current match. Solution for SQL Server: 1) Get total row count in my table. For eg. select count(1) from MYTABLE -- 500 rows. 2) Use CTE to assign logical row number for each row in my table. 3) Select number of rows using row number interval and BETWEEN clause. WITH CTE AS (SELECT ROW_NUMBER() OVER(order by MyColumn1, MyColumn2, MyColumn3) AS Row#, t.*
- Why not using a
MAX(version)? You don't need to
GROUP BYit. The
HAVING count(*) >= 2works for
count(*)), you can just test it modifing the count(*)>=400 to check that only retrieve the
- Specify your MySQL version.
- @Roy I don't need the MAX(version) I need the previous version to the last version for each instance that has more than two versions.
- @Akina MariaDB version 10.4.10
- If you only have 1 version do you want it or not?