CODEWITHSUNDEEP
sql
Metaller
Metaller

Reputation: 514

SQL query choose last row

I have a table in SQL Server database with the following structure:

enter image description here

I want to make a query that will choose last rows that are earlier than the parameter date I enter, grouped by ID_Club, it means, that the result of the query will be like:

4, 1, 166013.01, 343697.42, 748231.00, 2011-02-28,
8, 2, 331408.11, 6981028.26, 738000.00, 2011-02-28,
etc...

I can make similar query not with last row but with sum. It looks like:

select ID_Club, SUM(Cash), SUM(Less), SUM(InWay) 
from dbo.Period 
where DateEnd<'2011-03-01' 
GROUP BY ID_Club

Upvotes: 2

Views: 1131

Answers (2)

StuartLC
StuartLC

Reputation: 107237

Have a look at the SQL OVER clause when used with PARTITIONs

SELECT ID_Club, DateStart, DateEnd, Cash, Less, InWay
FROM Period p 
    INNER JOIN 
        (SELECT ID_Period, ROW_NUMBER() OVER (PARTITION by ID_CLUB ORDER BY ID_Period DESC) as RowNum
         FROM Period
               WHERE DateEnd<'2011-03-01') AS x
         ON p.ID_Period = x.ID_Period
WHERE x.RowNum = 1 -- Find just the last row in each partition

Your "SUM" query will fail if there are any other non-zero period records for the club in your given date range

Out of interest, the next version of SQL (Denali) allows you to ask for "FIRST" or "LAST" record in a partition, thus saving you the effort of the nested join

Update : Test DDL / DML

create table Period 
(
ID_Period INT, 
ID_Club INT, 
DateStart DATETIME, 
DateEnd DATETIME, 
Cash MONEY, 
Less MONEY,
InWay MONEY
)

insert into Period (ID_Period, ID_Club, DateStart, DateEnd, Cash, Less, InWay )
VALUES (1, 1, 2010-08-01, 2010-08-31, 0, 0, 0)
insert into Period (ID_Period, ID_Club, DateStart, DateEnd, Cash, Less, InWay )
VALUES (2, 1, 2010-08-01, 2010-08-31, 166013.01, 343697.42, 748231.00)
insert into Period (ID_Period, ID_Club, DateStart, DateEnd, Cash, Less, InWay )
VALUES (3, 2, 2010-08-01, 2010-08-31, 0, 0, 0)
insert into Period (ID_Period, ID_Club, DateStart, DateEnd, Cash, Less, InWay )
VALUES (4, 2, 2010-08-01, 2010-08-31, 331408.11, 6981028.26, 738000.00 )

Upvotes: 1

Marc B
Marc B

Reputation: 360572

Not quite sure how to read your question. You want only the most recent record BEFORE the specified date? Or all the records before the date?

If it's all the records, then simply remove the aggregate functions and remove the group-by clause, ending up with:

SELECT * FROM dbo.Period WHERE DateEnd < '2011-03-01';

If you want only the most recent record:

SELECT TOP 1 * FROM dbo.Period WHERE DateEnd < '2011-03-01' ORDER BY DateEnd DESC;

Upvotes: 3

Related Questions