Is there something like SELECT LAST in sql query?

Question

I use the sybase database to request a daily transaction report. I had a subquery in my script.

Here it is:

SELECT orders.accountid ,items.x,etc (SELECT charges.mistotal FROM charges where items.id = charges.id) FROM items,orders WHERE date = '2008-10-02' 

Here I get the error message as:

Subquery cannot return more than one value

My values: 7.50, 25.00

I want to return 25.00, but when I use

 (SELECT TOP 1 charges.mistotal FROM charges where items.id = charges.id) 

My result is 7.50, but I want to return 25.00

Does anyone have a better suggestion?

Answer 1

 SELECT TOP 1 * FROM dbo.YourTable ORDER BY Col DESC 

In your case, I think it will be

 SELECT TOP 1 charges.mistotal FROM charges where items.id = charges.id ORDER BY charges.mistotal DESC 

Answer 2

What are your criteria for choosing 25.00 instead of 7.5?

If this is due to the maximum value, you can try using the MAX () function in this field.

If this is due to the added chronological last line, try using MAX () in the datetime field if you have data on the hours and minutes that have been added.

Answer 3

You can try the following:

 SELECT MAX(charges.mistotal) FROM charges WHERE items.id = charges.id 

Answer 4

So you can use the reverse order:

 (SELECT TOP 1 charges.mistotal FROM charges WHERE items.id = charges.id ORDER BY charges.mistotal DESC ) 

In fact, since you did not specify an explicit order, the sequence of returned results is undefined, and you are just lucky that it gave you an answer that you did not want; he could give you the answer you wanted, and then you may not have noticed that this is not always right until it has entered production.

Or, can you use:

 (SELECT MAX(charges.mistotal) FROM charges WHERE charges.id = items.id ) 

Or do you really want SUM?

Answer 5

For first use, select top 1 | first * from the table sort in ascending order to get the last one, just invert your order.

Answer 6

CHOOSE TOP 1 charges. Due to failures, where items.id = charges.id ORDER BY charges.id DESC

The order by clause ensures that it returns in id order, and DESC means descent, so it will give you the largest (newest) value. TOP 1, of course, ensures that you just get one.

Answer 7

Sort the subquery. If you want the “last” value, you need to determine how you determine which item comes last (remember that SQL result sets are unordered by default).

For example:

 (SELECT TOP 1 charges.mistotal FROM charges where items.id = charges.id ORDER BY charges.mistotal DESC) 

This will return 25.00 instead of 7.50 (from your sample data above), but I assume that you want this value to be "last" because it is larger. Perhaps there is another area for which sorting is more suitable for you; maybe you have a timestamp column, for example, and you can sort it to get the most recent value instead of the largest value. The key simply defines what you mean by "last".