Is there any thing such as SELECT LAST in sql query?

Question

I am using sybase database to query the daily transaction report. I had subquery within my script.

Here as it goes:

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 am getting the error message as Subquery cannot return more than one values.

My values are 7.50, 25.00

I want to return the 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 has any better suggestion....

Answer 1

Under what criteria you choose to select the 25.00 instead of the 7.5?

If its related to the maximum value, you can try using the MAX() function on that field.

If its related to the chronologically last row added, try using the MAX() on the datetime field, if you have details on the hours and minutes it was added.

Answer 2

You could try this:

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

Answer 3

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

In your case, I guess that would be

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

Answer 4

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

The order by clause will make sure it comes back in the order of the id, and the DESC means descending so it will give you the largest (newest) value first. TOP 1 of course makes sure you just get that one.

Answer 5

Sort your subquery. If you want the "last" value, you need to define how you determine which item comes last (remember, 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 would return 25.00 instead of 7.50 (from your data examples above), but I'm assuming that you want this value to be "last" because it's bigger. There may be some other field that it makes more sense for you to sort on; maybe you have a timestamp column, for example, and you could sort on that to get the most recent value instead of the largest value. The key is just defining what you mean by "last".

Answer 6

So, can you use inverse order:

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

Actually, since you didn't give an explicit order, the sequence of the returned results is undefined, and you are just lucky that it gave you the answer you didn't want; it could have given you the answer you wanted, and then you might not have noticed that it was not always correct until after it went into production.

Or, can you use:

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

Or did you really want a SUM?

Answer 7

To get first you use select top 1 | first * from table order ascending to get last, just invert your order.