Sometimes, we want to get the ID of the last updated row in MySQL.

In this article, we’ll look at how to get the ID of the last updated row in MySQL.

How to get the ID of the last updated row in MySQL?

To get the ID of the last updated row in MySQL, we can define a variable that has the ID of the entry we’re updating.

For instance, we write

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND (SELECT @uids := CONCAT_WS(',', fooid, @uids));
SELECT @uids;

to define the @uids variable that has the ID of the rows we’re updating.

We assign the value for @uids after the AND.

We get all the rows we’re updating with

SELECT @uids := CONCAT_WS(',', fooid, @uids)

@uids is a string with the IDs of the rows we’re updating separated by commas.

Conclusion

To get the ID of the last updated row in MySQL, we can define a variable that has the ID of the entry we’re updating.

Sometimes, we want to use MySQL query to get column names of a table.

In this article, we’ll look at how to use MySQL query to get column names of a table.

How to use MySQL query to get column names of a table?

To use MySQL query to get column names of a table, we can select from the INFORMATION_SCHEMA.COLUMNS table.

For instance, we write

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

to select the column names of the yourtablename table in the yourdataname table.

COnclusion

To use MySQL query to get column names of a table, we can select from the INFORMATION_SCHEMA.COLUMNS table.

Sometimes, we want to get the top 1 row of each group with SQL.

In this article, we’ll look at how to get the top 1 row of each group with SQL.

How to get the top 1 row of each group with SQL?

To get the top 1 row of each group with SQL, we can use a subquery to assign a row number for each group item.

And then we can use WHERE to select the top row.

For instance, we write

WITH groups AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM logs
)
SELECT *
FROM groups
WHERE rn = 1

to assign a row number for each row with ROW_NUMBER.

And we divide the rows into groups with PARTITION .

We name the subquery with WITH and we set the name to groups.

Next, we get the row from each group with rn row number set to 1 to get the top row of each group.

Conclusion

To get the top 1 row of each group with SQL, we can use a subquery to assign a row number for each group item.

And then we can use WHERE to select the top row.

Sometimes, we want to fetch the row which has the max value for a column with SQL.

In this article, we’ll look at how to fetch the row which has the max value for a column with SQL.

How to fetch the row which has the max value for a column with SQL?

To fetch the row which has the max value for a column with SQL, we can do a self join.

For instance, we write

SELECT t1.*
FROM table t1
  LEFT OUTER JOIN table t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

to do a left join with to get the row with the max Date column value with

LEFT OUTER JOIN table  t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")

Date is in quotes because it’s a reserved word.

We use (t1.UserId = t2.UserId AND t1."Date" < t2."Date") to match the values with the same UserId but a greater Date value.

Conclusion

To fetch the row which has the max value for a column with SQL, we can do a self join.

Sometimes, we want to concatenate text from multiple rows into a single text string in SQL Server.

In this article, we’ll look at how to concatenate text from multiple rows into a single text string in SQL Server.

How to concatenate text from multiple rows into a single text string in SQL Server?

To concatenate text from multiple rows into a single text string in SQL Server, we can use the COALESCE function.

For instance, w write

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

to combine the @Names string with a commas and an empty string with the Name column value from the Peole table to combine the trings from Name together into one string.

Conclusion

To concatenate text from multiple rows into a single text string in SQL Server, we can use the COALESCE function.

Sometimes, we want to retrieve the last record in each group with MySQL.

In this article, we’ll look at how to retrieve the last record in each group with MySQL.

How to retrieve the last record in each group with MySQL?

To retrieve the last record in each group with MySQL, we can use the WITH statement.

For instance, we write

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

to select the rows with in the subquery with

SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM items AS m

And then we name that rank_messages.

We get the row number with of each row so that we can select the ones that’s last by ordering the selected rows in the subquery in descending order by the row number.

We get the row number with the ROW_NUMBER function.

And we use WITH to name the subquery ranked_messages.

Thewn we select the last rows from the subquery with

SELECT * FROM ranked_items WHERE rn = 1;

to return the last record of each group.

Conclusion

To retrieve the last record in each group with MySQL, we can use the WITH statement.

Sometimes, we want to select only rows with max value on a column with SQL.

In this article, we’ll look at how to select only rows with max value on a column with SQL.

How to select only rows with max value on a column with SQL?

To select only rows with max value on a column with SQL, we can use the MAX function.

For instance, we write

SELECT a.id, a.rev, a.contents
FROM table a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM table 
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

to seelct the rows with the max valur with the subquery result that we joined YourTable WITH.

And then we select the required columns from YourTable with

SELECT a.id, a.rev, a.contents
FROM table a

We do the inner join on the id and rev columns with

ON a.id = b.id AND a.rev = b.rev

Conclusion

To select only rows with max value on a column with SQL, we can use the MAX function.