Find Last Row in Group by Query-SQL Server

Find last row in group by query-SQL Server

select ID, Name 
FROM (select ID, Name, -- add other columns here
             ROW_NUMBER() over (partition by Name ORDER BY ID DESC) as MAX_ID
      from Customer) x
WHERE MAX_ID = 1

How to select the last record of each ID

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

How to get the first and the last record per group in SQL Server 2008?

How about using ROW_NUMBER:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RnAsc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val),
        RnDesc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val DESC)
    FROM tbl
)
SELECT
    id, [group], val, start, [end]
FROM Cte
WHERE
    RnAsc = 1 OR RnDesc = 1
ORDER BY [group], val

Retrieving the last record in each group - MySQL

MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries:

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

This and other approaches to finding groupwise maximal rows are illustrated in the MySQL manual.

Below is the original answer I wrote for this question in 2009:

---

I write the solution this way:

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

Regarding performance, one solution or the other can be better, depending on the nature of your data. So you should test both queries and use the one that is better at performance given your database.

For example, I have a copy of the StackOverflow August data dump. I'll use that for benchmarking. There are 1,114,357 rows in the Posts table. This is running on MySQL 5.0.75 on my Macbook Pro 2.40GHz.

I'll write a query to find the most recent post for a given user ID (mine).

First using the technique shown by @Eric with the GROUP BY in a subquery:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

Even the EXPLAIN analysis takes over 16 seconds:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

Now produce the same query result using my technique with LEFT JOIN:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

The EXPLAIN analysis shows that both tables are able to use their indexes:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

---

Here's the DDL for my Posts table:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

---

Note to commenters: If you want another benchmark with a different version of MySQL, a different dataset, or different table design, feel free to do it yourself. I have shown the technique above. Stack Overflow is here to show you how to do software development work, not to do all the work for you.

How to get the last row in the table using group by with Order by DESC?

One approach uses a GROUP BY query:

SELECT tla1.*, tb.*
FROM tbl_brands tb
INNER JOIN tbl_loader_attachment tla1
    ON tb.b_id = tla1.b_id
INNER JOIN
(
    SELECT b_id, MAX(la_id) AS max_la_id
    FROM tbl_loader_attachment
    GROUP BY b_id
) tla2
    ON tla1.b_id = tla2.b_id AND
       tla1.la_id = tla2.max_la_id;

If you are using MySQL 8+ (or should a future reader of this question be using MySQL 8+), then another option here is to use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY la_id DESC) rn
    FROM tbl_loader_attachment
)

SELECT tla.*, tb.*
FROM tbl_brands tb
INNER JOIN cte tla ON tb.b_id = tla.b_id
WHERE tla.rn = 1;

SQL Select last row from row that have same column

You can use ROW_NUMBER() function to generate row number partitioned by tp_product then order by post_numb descending; with consideration that the largest post_numb will be the "latest". Make that as subquery and do a WHERE Rnum=1 to only return the ones being deemed as "latest":

SELECT ID, post, tp_product, post_numb
   FROM
(SELECT ID, post, tp_product, post_numb,
       ROW_NUMBER() OVER (PARTITION BY tp_product ORDER BY post_numb DESC) AS Rnum
    FROM tp_posts) v
    WHERE Rnum=1;

However, this only work with MySQL v8+ and MariaDB v10.2+.

Demo

Get the only the last row in a sequence. SQL Server

A simple way is a correlated subquery:

select t.*
from t
where t.seq = (select max(t2.seq) from t t2 where t2.prod = t.prod);

For performance, you want an index on (prod, seq).

The above often has the best performance. But another way to write the query is to use window fucntions:

select t.*
from (select t.*, row_number() over (partition by prod order by seq desc) as seqnum
      from t
     ) t
where seqnum = 1;

How to get first and last record from same group in SQL Server?

You have a sequence number so you can use some form of aggregation. One method is:

select records,
       max(case when sequence = 1 then fromregion end) as fromregion,
       max(case when sequence = maxsequence then toregion) as toregion
from (select t.*, max(sequence) over (partition by records) as max_sequence
      from t
     ) t
group by records;

Unfortunately, SQL Server doesn't offer "first()" or "last()" as aggregation functions. But it does support first_value() as a window function. This allows you to do the logic without a subquery:

select distinct records,
       first_value(fromRegion) over (partition by records order by sequence) as fromregion,
       first_value(toRegion) over (partition by records order by sequence desc) as toregion
from t;

getting second to last record per group in SQL

SELECT * 
FROM (
select job AS qjob
      , id AS qid
      ,ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY ID DESC) AS RN
from sqbclog
)Sub
WHERE rn <= 2

This query will return last two records but if you only need the 2nd last record then in where clause use where en = 2 else leave it as it is.

SQL query to get 2nd last row

The query below will get you the second to last user based on the id. It achieves this by first ordering by id in descending order, using the OFFSET 1 to skip the new first row in the order i.e. User 4 and the LIMIT 1 to get just User 3 the next remaining user. The general pattern as shown in the fiddle which you can run online is to update the OFFSET

Schema (MySQL v5.7)

CREATE TABLE User (
  `id` INTEGER,
  `name` VARCHAR(6)
);

INSERT INTO User
  (`id`, `name`)
VALUES
  ('1', 'User 1'),
  ('2', 'User 2'),
  ('3', 'User 3'),
  ('4', 'User 4');

---

Query #1

SELECT "GET 2nd to Last";

---