First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

>

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

--

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)