Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

select last record for each customer


I have table with many recodrs as this table:
customerID      Date           Stock1  Stock2  Stock3  Stock4
112105222   22/01/2006        5            6           2          1
112105222   23/05/2006        4            8           6          7
112105222   01/02/2007        6            9           4          6
112101024   12/12/2007        7            8           9          1
112101024   15/02/2007        7            9           2          1
      .                 .
      .                 .
      .                 .

by select qury I want show just the last record for each customerID as this
table

customerID      Date           Stock1  Stock2  Stock3  Stock4
112105222    01/02/2007        6            9         4           6
112101024    15/02/2007        7            9         2           1

Plz who is can help me in select qury

thanx

Won't the last record for the customer id 112101024 be corresponding
to date 12/12/2007??
Or do you want your last record to be the last entry in the table for
that customerid??

In the former case try this query :

CREATE TABLE #Details(CustomerID INT ,DateEntered SMALLDATETIME,Stock1
INT,Stock2 INT,Stock3 INT,Stock4 INT)

insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112105222,'01/22/2006',5,6,2,1)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112105222,'05/23/2006',4,8,6,7)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112105222,'12/12/2007',6,9,4,6)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112101024,'12/12/2007',7,8,9,1)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112101024,'02/15/2007',7,9,2,1)

select
dtl.CustomerID,dtl.DateEntered,dtl.Stock1,dtl.Stock2,dtl.Stock3,dtl.Stock4
from    #Details dtl
inner join
                (select CustomerID,max(DateEntered) as MaxDate
                from    #Details
                group by CustomerID
                ) tbl
on              dtl.CustomerID = tbl.CustomerID and
                dtl.DateEntered = tbl.MaxDate

If you are not looking for this and want the last entry in the table
for each customer to be displayed then probably you need to add an
Identity column in the #Details table and query for the maximum of the
the Identity column grouped by CustomerID as shown below :

CREATE TABLE #Details(CustomerID INT ,DateEntered SMALLDATETIME,Stock1
INT,Stock2 INT,Stock3 INT,Stock4 INT, Idntty INT IDENTITY)

insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112105222,'01/22/2006',5,6,2,1)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112105222,'05/23/2006',4,8,6,7)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112105222,'12/12/2007',6,9,4,6)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112101024,'12/12/2007',7,8,9,1)
insert into
#Details(CustomerID,DateEntered,Stock1,Stock2,Stock3,Stock4)
VALUES(112101024,'02/15/2007',7,9,2,1)

select
dtl.CustomerID,dtl.DateEntered,dtl.Stock1,dtl.Stock2,dtl.Stock3,dtl.Stock4
from    #Details dtl
inner join
                (select CustomerID,max(Idntty) as MaxID
                from    #Details
                group by CustomerID
                ) tbl
on              dtl.Idntty = tbl.MaxID

On Feb 24, 12:28 pm, "zmsaadeh" <u32014@uwe> wrote:

 I want the last entry in the table
for each customer to be displayed in view table .
I can make that without define IDNTTY because i work as view table

thanx

You can drop this in a derived table, then you can Select the max top 1 of
the row_number():

select ROW_NUMBER() OVER(ORDER BY DateEntered DESC)as RNum, * from #Details

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc