Select only the important records

[infinitizon]

Hi all,

I have a database (of overtime applications) that looks like this

apply_date  |   start_time     |   end_time       |   overtime_rsn                            |   1st lvl Sup
2012-11-14  |   13:00:00.000   |   18:00:00.000   |   Very good reason                        |   Rejected
2012-11-15  |   16:00:00.000   |   21:00:00.000   |   Why do you ask                          |   Not yet Approved
2012-11-20  |   09:00:00.000   |   14:00:00.000   |   Good for me                             |   Approved
2012-11-20  |   09:00:00.000   |   14:00:00.000   |   Good for me                             |   Approved
2012-11-20  |   09:00:00.000   |   15:00:00.000   |   So, so Good for me                      |   Approved
2012-11-21  |   11:00:00.000   |   17:00:00.000   |   A reason you must not underestimate     |   Approved

Notice the date 2012-11-20 is showing 3 times. This is as a result of the fact that the applicant updated the overtime twice.

What I want is a select statement that will show me a result like this

apply_date  |   start_time     |   end_time       |   overtime_rsn                            |   1st lvl Sup
2012-11-14  |   13:00:00.000   |   18:00:00.000   |   Very good reason                        |   Rejected
2012-11-15  |   16:00:00.000   |   21:00:00.000   |   Why do you ask                          |   Not yet Approved
2012-11-20  |   09:00:00.000   |   15:00:00.000   |   So, so Good for me                      |   Approved
2012-11-21  |   11:00:00.000   |   17:00:00.000   |   A reason you must not underestimate     |   Approved

Notice I now have only the last record of the date 2012-11-20 rather than the three dates. How can I call this kind of query.

I know it is something simple but I cant just figure it out right away.
Thanks in advance.

 

[imex]

Hi,

Try:

with CTe as
(
    select
        *,
        ROW_NUMBER() OVER(PARTITION BY apply_date ORDER BY end_time DESC) as RowNum
    from MyTable
)

select * from CTE
where RowNum = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[r937]

imex, why would you take the 1st row?

he said he wanted "only the last record of the date" and the example shows that

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[infinitizon]

Well, thanks guys,

I think imex's solution works fine. I think you can truely select the first row since ROW_NUMBER() OVER(PARTITION BY apply_date ORDER BY end_time DESC) as RowNum will give each distinct apply date a row number.

I however ORDER BY timestamp since I have a timestamp column (and it worked better than ORDER BY end_time)

Thanks again Imex

 

[imex]

Rudy,

We used a descending order to get the last row.


[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[r937]

DOH!!!

i am such a numpty sometimes...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon