sql how to select the next to last row of the groups with two or more rows

select first and last row sql
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!)

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:

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 BY it. The HAVING count(*) >= 2 works for cnt>2 (the count(*)), you can just test it modifing the count(*)>=400 to check that only retrieve the cnt>400
  • 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?