Как найти последнюю запись sql - Исправление недочетов и поиск решений вместе с Examum.ru

This is a sample code to select all records from a table. Can someone show me how to select the last record of that table?

select * from table

When I use: SELECT * FROM TABLE ORDER BY ID DESC LIMIT
I get this error: Line 1: Incorrect syntax near ‘LIMIT’.
This is the code I use:

private void LastRecord()
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HELPDESK_OUTLOOKConnectionString3"].ToString());

    conn.Open();
    SqlDataReader myReader = null;
    SqlCommand myCommand = new SqlCommand("SELECT * FROM HD_AANVRAGEN ORDER BY " +
                "aanvraag_id DESC LIMIT 1", conn);
    myReader = myCommand.ExecuteReader();
    while (myReader.Read())
    {
        TextBox1.Text = (myReader["aanvraag_id"].ToString());
        TextBox1.Text += (myReader["wijziging_nummer"].ToString());
        TextBox1.Text += (myReader["melding_id"].ToString());
        TextBox1.Text += (myReader["aanvraag_titel"].ToString());
        TextBox1.Text += (myReader["aanvraag_omschrijving"].ToString());
        TextBox1.Text += (myReader["doorlooptijd_id"].ToString());
        TextBox1.Text += (myReader["rapporteren"].ToString());
        TextBox1.Text += (myReader["werknemer_id"].ToString());
        TextBox1.Text += (myReader["outlook_id"].ToString());
    }
}

asked Mar 4, 2011 at 8:46

TassistoTassisto

9,77728 gold badges99 silver badges154 bronze badges

Without any further information, which Database etc the best we can do is something like

Sql Server

SELECT TOP 1 * FROM Table ORDER BY ID DESC

MySql

SELECT * FROM Table ORDER BY ID DESC LIMIT 1

answered Mar 4, 2011 at 8:48

Adriaan StanderAdriaan Stander

162k30 gold badges287 silver badges283 bronze badges

to get the last row of a SQL-Database use this sql string:

SELECT * FROM TableName WHERE id=(SELECT max(id) FROM TableName);

Output:

Last Line of your db!

answered Mar 21, 2017 at 10:51

Assuming you have an Id column:

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. You could do:

SELECT MAX(Id)
  FROM table

This will get you the highest Id number.

answered Mar 4, 2011 at 8:49

Neil KnightNeil Knight

47.2k24 gold badges129 silver badges188 bronze badges

SELECT * FROM TABLE ORDER BY ID DESC LIMIT 1

Yes this is mysql, SQL Server:

SELECT TOP 1 * FROM Table ORDER BY ID DESC

answered Mar 4, 2011 at 8:49

SimonSimon

9,15713 gold badges71 silver badges114 bronze badges

MS SQL Server has supported ANSI SQL FETCH FIRST for many years now:

SELECT * FROM TABLE
ORDER BY ID DESC 
OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY

(Works with most modern databases.)

answered Nov 6, 2020 at 9:30

jarlhjarlh

41.9k8 gold badges44 silver badges63 bronze badges

It is always a good practice in your table design to have an automatic row identifier, such as

 [RowID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL

, then you can identify your last row by

 select * from yourTable where rowID =  @@IDENTITY

answered Apr 25, 2019 at 1:16

Jenna LeafJenna Leaf

2,21521 silver badges29 bronze badges

If you have a self-incrementing field (say ID) then you can do something like:
SELECT * FROM foo WHERE ID = (SELECT max(ID) FROM foo)

answered Jan 21, 2020 at 4:49

BostoneBostone

36.7k39 gold badges166 silver badges226 bronze badges

Almost all answers assume the ID column is ordered (and perhaps auto incremented). There are situations, however, when the ID column is not ordered, hence the ORDER BY statement makes no sense.

The last inserted ID might not always be the highest ID, it is just the last (unique) entry.

One possible solution for such a situation is to create a row id on the fly:

SET @r = 0;
SELECT * FROM (SELECT *, (@r := @r + 1) AS r_id FROM my_table) AS tmp
    ORDER BY r_id DESC LIMIT 1;

answered Feb 4, 2021 at 11:09

AdrianAdrian

7347 silver badges17 bronze badges

SELECT * FROM table ORDER BY Id DESC LIMIT 1

answered Mar 4, 2011 at 8:49

The last is just the first when you reverse your ordering.

answered Mar 4, 2011 at 9:19

jejejeje

3,1813 gold badges25 silver badges41 bronze badges

If your table has no auto incremented value and otherwise has no good element to order on, you can get the arbitrary order of the items in any collection like this

SELECT
    [item]
FROM (
    SELECT
        *
        , ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY GETDATE()) 'RN'
    FROM [TABLE]
) RDR 
WHERE [RN] = (
    SELECT
        MAX([RN])
    FROM (
        SELECT
            *
            , ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY GETDATE()) 'RN'
        FROM [TABLE]
    ) RDR 
)

An important caveat is that the performance for this is going to be abysmal with larger sets of data.

answered Oct 13, 2021 at 18:28

Pow-IanPow-Ian

3,6071 gold badge21 silver badges31 bronze badges

In Oracle, you can do:

SELECT *
FROM (SELECT EMP.*,ROWNUM FROM EMP ORDER BY ROWNUM DESC)
WHERE ROWNUM=1;

This is one of the possible ways.

Tunaki

132k46 gold badges331 silver badges418 bronze badges

answered Sep 4, 2016 at 17:03

select ADU.itemid, ADU.startdate, internalcostprice 
from ADUITEMINTERNALCOSTPRICE ADU

right join

   (select max(STARTDATE) as Max_date, itemid 
   from ADUITEMINTERNALCOSTPRICE
   group by itemid) as A

on A.ITEMID = ADU.ITEMID
and startdate= Max_date

answered Mar 8, 2019 at 14:47

I think this should do it.

declare @x int;
select @x = max(id) from table_name;
select * from where id = @x;

answered May 4, 2019 at 11:11

$sql="SELECT tot_visit FROM visitors WHERE date = DATE(NOW()) - 1 into @s                
$conn->query($sql);
$sql = "INSERT INTO visitors (nbvisit_day,date,tot_visit) VALUES (1,CURRENT_DATE,@s+1)";
$conn->query($sql);

answered Nov 11, 2018 at 20:31

You can also do something like this:

SELECT LAST (column_name) AS LAST_CUSTOMER FROM table_name;

answered Feb 3, 2019 at 5:47

vikas95prasadvikas95prasad

1,2081 gold badge12 silver badges35 bronze badges

I upvoted Ricardo. Actually max is much efficient than sorting .
See the differences. its excellent.

I had to get the last row/update record (timeStamp)

`sqlite> select timeStamp from mypadatav2 order by timeStamp desc limit 1;
 2020-03-11 23:55:00
 Run Time: real 1.806 user 1.689242 sys 0.117062`

`sqlite> select max(timeStamp) from mypadatav2;
 2020-03-11 23:55:00
 Run Time: real 0.553 user 0.412618 sys 0.134340`

answered Mar 13, 2020 at 2:18

Источник

I have a table with several records. There is an id field. I would like to select the record with the most recent id (i.e. the highest id).

Any ideas?

Sébastien

11.8k11 gold badges56 silver badges78 bronze badges

asked Apr 17, 2010 at 17:12

SELECT * 
FROM table_name
ORDER BY id DESC
LIMIT 1

answered Apr 17, 2010 at 17:14

codaddictcodaddict

444k81 gold badges492 silver badges528 bronze badges

You could also do something like this:

SELECT tb1.* FROM Table tb1 WHERE id = (SELECT MAX(tb2.id) FROM Table tb2);

Its useful when you want to make some joins.

answered Apr 29, 2015 at 13:45

Luiz VidLuiz Vid

1211 silver badge2 bronze badges

User order by with desc order:

select * from t
order by id desc
limit 1

answered Apr 17, 2010 at 17:13

Andrew BezzubAndrew Bezzub

15.7k7 gold badges51 silver badges73 bronze badges

SELECT MAX("field name") AS ("primary key") FROM ("table name")

example:

SELECT MAX(brand) AS brandid FROM brand_tbl

James Webster

31.9k11 gold badges69 silver badges114 bronze badges

answered Sep 26, 2011 at 14:01

SELECT   *
FROM     table
ORDER BY id DESC
LIMIT    0, 1

answered Apr 17, 2010 at 17:15

yassinyassin

6,4717 gold badges34 silver badges39 bronze badges

I have used the following two:

1 - select id from table_name where id = (select MAX(id) from table_name)
2 - select id from table_name order by id desc limit 0, 1

answered Sep 20, 2017 at 10:44

SELECT * FROM your_table ORDER BY id ASC LIMIT 0, 1

The ASC will return resultset in ascending order thereby leaving you with the latest or most recent record. The DESC counterpart will do the exact opposite. That is, return the oldest record.

pushkin

9,43015 gold badges51 silver badges93 bronze badges

answered Sep 24, 2018 at 20:55

JDK BenJDK Ben

2112 silver badges5 bronze badges

Источник

In this SQL Server tutorial, we will discuss How to select records from a SQL Server Table, we will also cover Different scenarios on selecting records in SQL Server and will cover the following topic:

How to select latest record in SQL Server
How to select last record in SQL Server
How to select max record in SQL Server
How to select min record in SQL Server
How to select latest date records in SQL Server
How to select last inserted record in SQL Server
How to get last updated record in SQL Server
How to select last 10 records in SQL Server
How to select first and last record in SQL Server
How to select max date record in SQL Server
How to select last 1000 rows in SQL Server
How to get last week data in SQL Server
How to get last 6 months data in SQL Server

In this article, we are going to discuss some examples of How to select a particular record from a SQL Server table. Now, for the demonstration, we have to consider a table having some pre-populated data.

So, for most of the examples illustrated in this article, we are taking a table having 10 sample records. The sample table is given below.

Sample Table

How to select latest/last record in SQL Server
- How to select latest/last column in SQL Server
- How to select latest/last row in SQL Server
How to select max record in SQL Server
How to select min record in SQL Server
How to select first and last record in SQL Server
How to select last 10 records in SQL Server
How to select max date record in SQL Server
How to select latest date records in SQL Server
How to select last inserted record in SQL Server
How to get last updated record in SQL Server
How to select last 1000 rows in SQL Server
How to get last week data in SQL Server
How to get last 6 months data in SQL Server

In SQL Server, when we insert a record in a table without specifying any condition then, the record is added in the last of that table. So, we can also say that the latest record is the last record in a table.

So, in this section, we will learn how to select the last or latest record in the SQL Server table.

Now, there can be two scenarios, first, when we only need the last value from a column, and second, when we need the last inserted row. We will discuss the solution of both scenarios.

How to select latest/last column in SQL Server

To select the last value from the table column, we can follow the given approach.

First, select a column and restrict the number of results to only 1.
Next, order the output in descending order so that we can get the last record at the top.

Now, to implement this approach, we can use the following syntax.

SELECT TOP 1 column_name FROM table_name  
ORDER BY column_name DESC;

In the above syntax, we are using the SELECT statement to select the column. And we have also specified “TOP 1” which will limit the result to 1. Next, we are using the ORDER BY clause to arrange the result in descending order.

For the demonstration of this concept, consider the following example given below.

USE sqlserverguides
GO

SELECT TOP 1 [first_name], [gender], [country] FROM dbo.SampleTable  
ORDER BY [id] DESC; 
GO

In the example, we have selected 3 columns from the sample table shown at the starting of the post. And we have specified “TOP 1” to limit the number of results to one. After this, we are arranging the value of the table in descending order. We have ordered the table using a unique column id. And after successful execution of the query, we will get the following output.

Final Output

So, from the output, we can observe that the query has returned the last record from the table. The last record represents first_name as “Leontine“, gender as “Male“, and the country as “New Zealand“.

Read: Latest Record for Each User in SQL Server

How to select latest/last row in SQL Server

The approach to select the last row will be the same as mentioned in the previous topic. The only thing that needs to be modified is the column name. We need to specify all the column names instead of one column or use “*” to select all columns.

Here is the syntax that we can use to implement the above task.

SELECT TOP 1 * FROM table_name
ORDER BY column_name DESC; 
GO

Next, let’s implement the following query on our sample table.

USE sqlserverguides
GO

SELECT TOP 1 * FROM dbo.SampleTable  
ORDER BY [id] DESC; 
GO

And after executing the above example, we will get the complete data of the last row.

Output

So, from the output, we can observe that the query has returned the last record from the table. The last record represents first_name as “Leontine“, last_name as “Shevlan“, gender as “Male“, and the country as “New Zealand“.

Read SQL Server stored procedure vs function

How to select max record in SQL Server

While working as a DBA, you might get into a situation where you need to find the record with the highest value. Now, finding a single record with the highest value manually from thousand of records will be difficult.

So, in this section, we will discuss a method to find a record with maximum value in SQL Server.

Before going through the approach, consider the following sales table. We are going to use this sales table further in our example.

Sales Table

To select the recorded with the highest value, we have to use the WHERE clause and MAX() function in our query. The SQL Server MAX() function is used to find the highest value from a column. And the WHERE clause will help to filter the records where the value of the column is maximum. Here is the standard syntax that we can follow.

SELECT column_name, ... FROM table_name 

WHERE column_name = (SELECT MAX(column_name) FROM table_name)

The above syntax will return the selected column value from a table where the column value is maximum. Now, let’s use this syntax to find the maximum sales value from our sales table.

SELECT * FROM SalesTable 
WHERE [Sales] = (SELECT MAX([Sales]) FROM SalesTable)
GO

We are using the query to select all the column values from the sales table where the value in the sales column is maximum.

And after executing the above query on the sales table, we will get the following output.

Output

Read SQL Server create stored procedure (15 ways)

How to select min record in SQL Server

In the previous section, we have discussed the selection of a record with maximum value. Now, let’s discuss how we can select a record with a minimum value.

The technique for this task is very much similar to what we have done in the previous topic. We only need to replace the MAX() function with MIN() function. The MIN() function will help to find the minimum value from the selected column. And we can use this function in the WHERE clause to filter out the records where the column value is minimum.

We can use the following syntax to implement the task.

SELECT column_name, ... FROM table_name 
WHERE column_name = (SELECT MIN(column_name) FROM table_name)

The above syntax will help to select a record from a table where the column value is minimum. Now, let’s use this syntax to find the minimum sales value from our sales table.

SELECT * FROM SalesTable 
WHERE [Sales] = (SELECT MIN([Sales]) FROM SalesTable)
GO

After executing the above query on the sales table, we will get the record with a minimum sales value.

Output

Read: Select last 10 records in SQL Server without sorting

How to select first and last record in SQL Server

Till now, we have learned how to get the last record from a SQL Server table. Now, let’s understand how to select the first and last records of a table together.

There can different methods to select the first and last records of a table. In this section, we are going to understand a standard approach to achieve this task. The approach should be as follows.

First, use the SELECT statement to select the first record from a table.
Again, use another SELECT statement to get the last record from table.
In the end, combine both the results using UNION operator.

Here is the syntax that we can use to get the first and last records together.

SELECT * FROM ( SELECT TOP 1 * FROM table_name     
                ORDER BY column_name) first
UNION ALL
SELECT * FROM ( SELECT TOP 1 * FROM table_name 
                ORDER BY column_name DESC ) last

In SQL Server, we cannot directly use the UNION operator and ORDER BY clause together. For this implementation, we have to parenthesis. Now, let’s use this syntax to select the first and last records from our sample table.

SELECT * FROM ( SELECT TOP 1 * FROM SampleTable     
                ORDER BY [id]) first
UNION ALL
SELECT * FROM ( SELECT TOP 1 * FROM SampleTable 
                ORDER BY [id] DESC ) last

It is important to use a unique identity column in the ORDER BY clause. In our case, we are using the id column. After implementing the above example, we will get the following output.

How to select first and last record in SQL Server

Output

Read msg 3609 the transaction ended in the trigger

How to select last 10 records in SQL Server

In SQL Server, we can easily select the last 10 records from a table by using the “SELECT TOP” statement. The TOP clause in SQL Server is used to control the number or percentage of rows from the result. And to select the records from the last, we have to arrange the rows in descending order.

We can use the following script to implement the task and achieve the desired result.

SELECT TOP 10 * FROM table_name
ORDER BY column_name DESC

Usually, we have to implement this task where we have thousands of records in a table. But to simplify things, we are using a sample table that have only 20 records. And we will try to select the last 10 records from it.

Sample Table

Now, let’s use the given syntax to select the last 10 records from our sample table.

USE sqlserverguides
GO

SELECT TOP 10 * FROM MockTable
ORDER BY [id] DESC

After successfully implementing the above example, we will get the last 10 records from the sample table.

How to select last 10 records in SQL Server

Output

How to select max date record in SQL Server

In this topic, we are going to discuss selecting a record having a maximum (recent) date value.

As we already discussed in the previous topic, selecting the maximum value using the MAX() function. Similarly, we can easily use the MAX() function to find the column having the highest date value. Now, the highest date value basically means the most latest date value.

Here is the standard syntax that we can use to select the maximum date record in SQL Server.

SELECT * FROM table_name
WHERE column_name = (SELECT MAX(column_name) FROM table_name)

Now, let’s use the given syntax to select the recent date record from our sample table.

SELECT * FROM SampleTable
WHERE [Date] = (SELECT MAX([Date]) FROM SampleTable)

After executing the above example, the server will return the record with the highest date value in the table.

How to select max date record in SQL Server

Output

As there are 2 with the same highest value, the query has returned 2 records.

Read SQL Server trigger after insert with examples

How to select latest date records in SQL Server

While working with the SQL Server table, you might get into a situation where you have a date column with a large number of values. And you want the latest records from that table.

So, in this section, let’s discuss how to select the latest date records from a table in SQL Server.

The implementation of this task is very simple in SQL Server. For this, we need to arrange the date records of the table in descending order. So, the record with the latest date value will come at the top, and the record with the oldest date with come at the bottom.

Here is the syntax that we can use to get the latest date records in SQL Server.

Select column_name, .. From table_name 
Order By date_column Desc;

Now, let’s use the given syntax to select the last 10 records from our sample table.

Select * From SampleTable 
Order By [Date] Desc;

After executing the above example, the server will return the records in such a way that the records with the latest dates appear at the top.

select latest date records in SQL Server

Output

Read SQL Server convert integer to string + 12 Examples

How to select last inserted record in SQL Server

Directly there is no way in SQL Server to select the last inserted record. Still, in this section, we will try to understand a standard approach that we can use while working with tables in SQL Server.

While creating a table, a best practice is to have one identity column as the primary key in SQL Server. An identity column automatically increases its value when we insert a new record in the table. And, when we create an identity column as the primary key, that column is used to identify each record in the table uniquely.

In SQL Server, we can easily find the last identity value and then use it to fetch the last inserted record.

Let’s understand this implementation with the help of an example, and we will be using the sample table shown at the starting of the article. In the sample table, “id” is a primary identity column.

For demonstration, first, we will insert a new record in the sample table and then find and use the value of last identity value. And by using the last identity value, we will fetch the last inserted record in the table.

Now, we are going to use the following query to insert a new record in the sample table.

INSERT INTO SampleTable values('Dniren', 'Hoolaghan', 'Male', 'South Africa', GETDATE())

Next, we have to use the IDENT_CURRENT() function. This function accepts the table name and returns the last identity value generated for that table. In our case, this function will return 11 as output.

SELECT IDENT_CURRENT('dbo.SampleTable') AS 'LAST ID VALUE'

Output

Now, as we got the last identity value for our table. We can easily use this value as a condition in the WHERE clause to select the last inserted record. For this, we are executing the following query.

SELECT * FROM dbo.SampleTable WHERE [id] = (SELECT IDENT_CURRENT('dbo.SampleTable'))

In the end, we will get the following output.

How to select last inserted record in SQL Server

Output

Read SQL Server Convert Datetime to String

How to get last updated record in SQL Server

The simplest way to get the last updated record is by using the date or DateTime column in SQL Server. First, we should have a last modified column in SQL Server that will automatically have a DateTime value based upon the last modification. And then, we can easily find the last updated record either by arranging the records or by finding records with the highest modified date.

The best way to implement this task is by creating a trigger that will automatically update the last modified DateTime value based upon some change. For demonstration, let’s first create a trigger to execute the task for our sample table.

But, first, we have to modify our sample table and add a new column named “LastModified” which will hold the DateTime value. And, we are going to execute the following script to implement this task.

ALTER TABLE dbo.SampleTable
ADD [LastModified] datetime DEFAULT CURRENT_TIMESTAMP

Next, we are going to execute the following code to create the trigger.

CREATE TRIGGER trg_UpdateLastModified
ON dbo.SampleTable
AFTER UPDATE
AS
UPDATE dbo.SampleTable
SET LastModified = CURRENT_TIMESTAMP
WHERE id IN (SELECT DISTINCT id FROM INSERTED);

In the above query, we have created a trigger that will be executed only when we make some changes in our sample table. And it will automatically update the value of the LastModified column.

Now, let’s modify a record in the sample table, and we are executing the following query.

UPDATE dbo.SampleTable
SET [Country] = 'Australia' WHERE [first_name] = 'Annaliese'

In the above code, we are updating the country name of a record having the first name as “Annaliese“. After the modification is encountered, the trigger will automatically update the value of the “LastModified” column.

In the end, we can use the following query to find the last modified record in the sample table.

SELECT * FROM dbo.SampleTable 
WHERE [LastModified] = (SELECT MAX([LastModified]) FROM dbo.SampleTable)

The above query will return the last modified record as a result. And we will get the following output.

How to get last updated record in SQL Server

Output

How to select last 1000 rows in SQL Server

In SQL Server, we can easily restrict the number of rows that we want in the result by using the TOP statement. After this, we can arrange the rows in descending order so the rows from last will appear at the top.

Here is a general syntax that we can use to get the last 100 rows from the table.

SELECT TOP 1000 * FROM table_name 
ORDER BY identity_column DESC

Let’s implement the above approach on our sample table to fetch the last 1000 from it.

SELECT TOP 1000 * FROM dbo.SampleTable 
ORDER BY [id] DESC

In our case, the “id” is a unique identity column that is used to identify each row uniquely.

Read SQL Server drop table if exists

How to get last week data in SQL Server

In SQL Server, we do not have any direct function which will return last week’s data from the SQL Server table. Still, there is a simple approach that we can implement to get the desired data.

For this implementation, we have to the DATEADD() function in the WHERE clause of the statement. The DATEADD() function is used to add the date or time value to a specified date value, and then return the result.

We can use the DATEADD() function in the WHERE to specify the date condition after which we want the result. The syntax for this implementation is given below.

SELECT column_name, ... FROM table_name
WHERE date_column >= DATEADD(day,-7, GETDATE())

In the syntax, we are using the DATEADD() function in the WHERE clause to filter the records having dates from last week. The DATEADD() function subtracts 7 days from the current date and then the result is compared with the date column to get the result.

Let’s understand this implementation by executing a small example. For this, we will insert some data in the sample table with dates from the previous week.

INSERT INTO dbo.SampleTable
VALUES('Grady', 'Maddern', 'Male', 'Indonesia', '2021-07-15'),
      ('Maud', 'Antoni', 'Female', 'Russia', '2021-07-17');

Next, let’s use the given syntax to find last week’s records from the sample table.

SELECT * FROM SampleTable
WHERE [Date] >= DATEADD(day,-7, GETDATE())

The above query will return all the records where the date values in the “Date” column are from the previous week.

Output

How to get last 6 months data in SQL Server

The implementation of the query to get the last 6 months’ data is almost similar to what we have explained in the previous section.

We can also use the DATEADD() function to get the last 6 months’ data from a table by comparing the dates in the WHERE clause.

Here is the syntax that we can use to get the last 6 months’ data from the table.

SELECT column_name, ... FROM table_name
WHERE date_column >= DATEADD(MONTH,-6, GETDATE())

The DATEADD() function in the syntax will subtract 6 months from the current date and returns the result. The result from DATEADD() will be compared with the date column to get the result.

Let’s use the given syntax to find 6 months records from the sample table.

SELECT * FROM SampleTable
WHERE [Date] >= DATEADD(MONTH,-6, GETDATE())

After successfully executing the above example, we will get all the records from the last 6 months as a result.

How to get last 6 months data in SQL Server

Output

You may also like the following SQL server tutorials:

PostgreSQL vs SQL Server: Detailed Comparison
SQL Server stored procedure if else
IDENTITY_INSERT in SQL Server
SQL Server stored procedure parameters

So, in this tutorial, we have discussed How to select records from a SQL Server Table, Different scenarios on selecting records in SQL Server, and we have covered the following topic:

How to select latest record in SQL Server
How to select last record in SQL Server
How to select max record in SQL Server
How to select min record in SQL Server
How to select latest date records in SQL Server
How to select last inserted record in SQL Server
How to get last updated record in SQL Server
How to select last 10 records in SQL Server
How to select first and last record in SQL Server
How to select max date record in SQL Server
How to select last 1000 rows in SQL Server
How to get last week data in SQL Server
How to get last 6 months data in SQL Server

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.

Источник

next →
← prev

The LAST() function in Structured Query Language shows the last value from the specified column of the table.

Note: This SQL function is only supported in Microsoft Access database. Oracle supports ORDER BY and ROWNUM keywords, and MySQL supports the LIMIT keyword for selecting the last record.

Syntax of LAST() Function

In the above syntax, the LAST keyword denotes the last row to be shown from the table in the output, and the Field_Name denotes the column whose value we want to show.

Example of the LAST function in SQL

Example 1:

Firstly, we have to create a table and insert the data into the table in SQL.

The following SQL statement creates the Student_Details table with Student_ID as the primary key:

The following SQL queries insert the record of students into the above table using INSERT INTO statement:

Let’s see the record of the above table using the following SELECT statement:

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	99
138	Mohit	MCA	21	92

The following query shows the last Student_Name from the above table in the output:

Output:

Syntax of LIMIT Clause in MySQL

In this MySQL syntax, we have to specify the value 1 just after the LIMIT keyword for indicating the single row/record.

Example of LIMIT Clause in MySQL

Let’s take the following Employee table to explain how to use the LIMIT clause in MySQL for accessing the last record:

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_Bonus
101	Anuj	Ghaziabad	35000	2000
102	Tushar	Lucknow	29000	3000
103	Vivek	Kolkata	35000	2500
104	Shivam	Goa	22000	3000

The following MySQL query shows the last value of the Emp_City column from the above Employee table:

Output:

Goa

ROWNUM keyword in Oracle

The syntax for accessing the last record from the Oracle database is given below:

In this Oracle syntax, we have to specify the ROWNUM keyword, which is less than and equal to 1. In Oracle, the ROWNUM keyword is used in the WHERE clause for retrieving the last record from the table.

Example of ROWNUM Clause in Oracle

Let’s take the following Cars table to explain how to use the ROWNUM keyword in MySQL:

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	900000
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Nexon	7	1000000

The following MySQL query shows the last name of the car from the Car_Name column of the Cars table:

Output:

Nexon

Next TopicSQL SELECT RANDOM

← prev
next →

Источник

Как получить последнюю запись для каждого значения определённого поля?

Есть таблица balances:

account_id;
date;
balance;

Хочу получить для каждого account_id последнюю запись.

Следующий запрос выдаёт ошибку:

SELECT account_id, max(date), balance
FROM account_balances GROUP BY account_id;

Текст ошибки:

[42000][1055] Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'core.account_balances.balance' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Я понимаю, что я запрашиваю неаггрегированое поле, но как мне иначе получить последний баланс каждого счета?

Версия mysql: 5.7.11

Настройки дефолтные. После установки ничего не менял.

Вопрос задан

более трёх лет назад
23461 просмотр

SELECT * FROM account_balances AS ab
WHERE date =
  (SELECT MAX(date) FROM account_balances AS ab2 WHERE ab.account_id = ab2.account_id) AND
ORDER BY ab.account_id

Пригласить эксперта

А из чего у вас ключ таблицы состоит? На мой взгляд у вас здесь архитектурная погрешность.
Предположим что из id и даты. Тогда есть примерно такое решение

CREATE TEMPORARY TABLE t1 (select account_id,MAX(date) as date from t2 GROUP BY account_id);

SELECT account_id,date,balance FROM table3 as t3
INNER JOIN t1 ON t1.account_id = t3.account_id AND t1.date = t3.date

у тебя select account_id, max(date), balance ..
а группировка только по account_id. (с такой группировкой по одному account_id надо к balance надо применить какую то агр функцию, например max(), тогда запустится, но результат будет не такой как ты хочешь =) )

вот так сделай:

select 
	ad.*, 
	ab.balance 
from
(
	select
		account_id, max(date) as max_date
	from tmp_account_balance
	group by account_id
) ad
inner join tmp_account_balance ab on ad.account_id = ab.account_id and ab.date = ad.max_date;

MySQL какой? и какие стоят настройки? там в 5.7 много чего наворотили…

Показать ещё
Загружается…

29 мая 2023, в 08:48

5000 руб./за проект

29 мая 2023, в 08:29

3000 руб./за проект

29 мая 2023, в 08:27

2000 руб./за проект

Минуточку внимания

Источник