如何在不使用MAX / TOP子句的情况下获取sql server中的最后一个记录值? [英] how to get last record value in sql server without using MAX/TOP clause?
问题描述
亲爱的所有人,
我最近在采访中对上述问题大加注意。这个问题我曾多次面对,但我对这个问题没有解决方案。所以请考虑下面的例子并给我一个正确的答案,这将是一个很大的帮助。
示例:
表名 - emprecord 。
第一列(主键) - empid (此ID没有类似于字符数字的模式,所以请考虑它们可能是此字段中的任何内容,如 123yuio6yu 成为第一个这样的id)
基于以上的事情,如何在不使用 MAX的情况下获得此表中的最后一条记录或 TOP ?和数据库 没有名为ID的IDENTITY列
先谢谢。
亲爱的,
在这种情况下我们应该使用动态游标: -
DECLARE @ MYVAR NVARCHAR (100 )
DECLARE MYTESTCURSOR CURSOR
DYNAMIC
FOR
SELECT MESSAGE FROM ELMAH_ERROR
OPEN MYTESTCURSOR
FETCH 最后 FROM MYTESTCURSOR INTO @ MYVAR
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR
SELECT @ MYVAR
以上代码经过测试......
嗨!
LAST()函数
LAST()函数返回最后一个值选中的列。
SQL LAST()语法
SELECT 最后(column_name) FROM table_name
SQL LAST()示例
我们有以下订单表:< br $>
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600尼尔森
3 2008/09/02 700汉森
4 2008/09/03 300汉森
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
现在我们要查找OrderPrice列的最后一个值。
我们使用以下SQL语句:
SELECT 最后(OrderPrice) AS LastOrderPrice FROM 订单
提示:如果不支持LAST()函数,则解决方法:
< pre lang =SQL> SELECT OrderPrice FROM 订单订购 BY O_Id DESC LIMIT 1
结果集如下所示:
LastOrderPrice
100
也许他们的意思是这个关键字:ROWCOUNT
,在运行语句之前设置它会将结果集大小限制在提供的值中最后设置rowcount
。
所以你应该有这样的东西:
set rowcount 1
选择 * 来自 emprecord 订单 按 empiddesc
这将为您提供按其empid排序的最后一名员工。
Dear All,
I got bold on the above question in an interview recently. This question i faced many times but yet i have no solution for the question. So please consider the following example and provide me a proper answer of the question this will be a great help.
Example:
table name - emprecord.
first column (primary key)- empid (this id has no pattern like numeric of character-base so consider that their may be anything in this field like 123yuio6yu will be the first id like this )
based on the above things how will i get the last record in this table without using MAX or TOP? and the database without having an IDENTITY column named ID
Thanks in Advance.
Dear,
In that scenario we should use dynamic cursor: -
DECLARE @MYVAR NVARCHAR(100) DECLARE MYTESTCURSOR CURSOR DYNAMIC FOR SELECT MESSAGE FROM ELMAH_ERROR OPEN MYTESTCURSOR FETCH LAST FROM MYTESTCURSOR INTO @MYVAR CLOSE MYTESTCURSOR DEALLOCATE MYTESTCURSOR SELECT @MYVAR
The above code is tested......
Hi!
The LAST() Function
The LAST() function returns the last value of the selected column.
SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name
SQL LAST() Example
We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the last value of the "OrderPrice" column.
We use the following SQL statement:
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
Tip: Workaround if LAST() function is not supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1
The result-set will look like this:
LastOrderPrice
100
Maybe they meant this keyword :ROWCOUNT
, setting it before running a statement will confine the result set size to the provided value in lastset rowcount
.
So you should have something like this :
set rowcount 1 select * from emprecord order by empid desc
This will give you last employee sorted by their empid.
这篇关于如何在不使用MAX / TOP子句的情况下获取sql server中的最后一个记录值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!