1

I have to enter a value of the last row of a table to another table.Therefore within the insert query i've used to select query to get the value i want. The query executes without any error. But it gives the 1st row of the table instead of the last row.

Here is my query 

SELECT top 1 ref_no FROM MASTER_CAL
Mike
  • 1,017
  • 4
  • 19
  • 34

10 Answers10

5
SELECT TOP 1 * FROM Table ORDER BY ID DESC
or
SELECT TOP 1 * FROM Table ORDER BY POST_DATE DESC
KyLim
  • 468
  • 1
  • 6
  • 22
2

Your Last row depends upon your sorting order.

To make sure, you get correct result each time you can do something like this.

SELECT top 1 ref_no FROM MASTER_CAL ORDER BY ID DESC
10K35H 5H4KY4
  • 1,496
  • 5
  • 19
  • 41
1

Maybe it's work if you change the order to Descending.

for example : 

SELECT top 1 ref_no FROM MASTER_CAL ORDER BY ID DESC;
Dita Aji Pratama
  • 710
  • 1
  • 12
  • 28
0

Use an ORDER BY clause to get your desired result.

If your table have an incremental ID Colum sort the table using that , else number ur table using some functions like row_ number and and sort the number column.

Assuming your table is having identity column id.

 SELECT top 1 ref_no
  FROM MASTER_CALL
  ORDER BY ID DESC

If you table don't have an identity column, use below script.

 with cte_1
 As ( SELECT ref_no, ROW_NUBER( ORDER BY (select 1)) RNO
  FROM MASTER_CALL)
 SELECT ref_no 
 FROM cte_1
  ORDER BY  RNO DESC
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
0

I found the answe for this.
This is my query and it works perfectly. 

SELECT top 1 ref_no FROM MASTER_CAL order by ref_no desc
Mike
  • 1,017
  • 4
  • 19
  • 34
0

If you have an indexed column let's say 'ref_no', then use

SELECT top 1 ref_no FROM MASTER_CAL order by ref_no desc

Or if you don't have such thing you'll have make extra column row_number() as follows:

select top 1 a.ref_no
from
(
    select SlNo=row_number() over(order by (select 1)), ref_no 
    from MASTER_CAL
) a
order by slno desc
Gomes
  • 36
  • 6
0

insert into temp table at the same time, and select from that

It works even if you don't have auto increment column

DECLARE @table table (id int)

INSERT INTO MASTER_CAL
OUTPUT inserted.id into @table
VALUES('XYZ')

SELECT id from @table
Munavvar
  • 802
  • 1
  • 11
  • 33
0
SELECT  ref_no FROM MASTER_CAL ORDER BY ID DESC limit 0,1

I think your AUTO_INCREMENT field is ID and table name is MASTER_CAL.Then above code will help you to find last inserted row in this table.

Aswathy
  • 211
  • 2
  • 9
0

In your table ref_no column set as Identity Increment = 1 then you can try this query:

SELECT TOP 1 ref_no FROM MASTER_CAL ORDER BY ref_no DESC

This query gives exact result which you want.

Krunal Mevada
  • 1,637
  • 1
  • 17
  • 28
-1

Try this : SELECT ref_no FROM MASTER_CAL ORDER BY ref_no DESC LIMIT 1

zaidysf
  • 492
  • 2
  • 14