CODEWITHSUNDEEP
c#sqlsql-servert-sql
x-code
x-code

Reputation: 608

sql query between time and a DISTINCT column

my table for a vehicle looks like

vehNo   tTime                     odo     
ABC     2013-02-13 10:30:00       80       
ABC     2013-02-12 10:30:00       10      
ABC     2013-02-12 12:30:00       30      
ABC     2013-02-13 01:30:00       40      
ABC     2013-02-13 02:30:00       40 
ABC     2013-02-13 03:30:00       40 
XYZ     2013-02-13 03:33:00       44
ABC     2013-02-13 04:30:00       60 
ABC     2013-02-13 11:30:00       100

i can give between time 2013-02-12 10:30:00 and 2013-02-13 10:30:00 (order by time)but how do i get DISTINCT odo like

ABC     2013-02-12 10:30:00       10 
ABC     2013-02-12 12:30:00       30 
ABC     2013-02-13 03:30:00       40         (time being latest, ignoring 1:30 & 2:30)
ABC     2013-02-13 04:30:00       60
ABC     2013-02-13 10:30:00       80

im running on MSSql server 2005

Upvotes: 3

Views: 155

Answers (6)

x-code
x-code

Reputation: 608

ok with @mark i extended my solution as

select vehNo, max(tTime) as [tTime], odo
from Table_1
where vehNo = 'ABC' and tTime between '2013-02-12 10:30:00' and '2013-02-13 10:30:00'
group by vehNo, odo
order by vehNo, odo

Upvotes: 1

Soner Gönül
Soner Gönül

Reputation: 98810

Try like this;

select vehNo, max(tTime) as tTime, odo
from myTable
group by vehNo, odo
order by vehNo, odo

| VEHNO |               TTIME | ODO |
-------------------------------------
|   ABC | 2013-02-12 10:30:00 |  10 |
|   ABC | 2013-02-12 12:30:00 |  30 |
|   ABC | 2013-02-13 03:30:00 |  40 |
|   ABC | 2013-02-13 04:30:00 |  60 |
|   ABC | 2013-02-13 10:30:00 |  80 |
|   ABC | 2013-02-13 11:30:00 | 100 |

Here is SQL Fiddle DEMO.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1063433

select vehNo, max(tTime) as [tTime], odo
from vehicles
group by vehNo, odo
order by vehNo, odo

Obviously, for the between "2013-02-12 10:30:00 and 2013-02-13 10:30:00" you can add a where clause in the middle:

select vehNo, max(tTime) as [tTime], odo
from vehicles
where tTime between @start and @end
group by vehNo, odo
order by vehNo, odo

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65304

SELECT
  vehNO, 
  MAX(tTime) AS tTime,
  MAX(odo) AS odo
FROM vehicle
WHERE ...
GROUP BY ...

Upvotes: 0

John Woo
John Woo

Reputation: 263813

SQL Server 2005 already supports Common Table Expression and Window Function. With the help of ROW_NUMBER() it ranks the records from a group by specifying the order of the records.

WITH latestDate
AS
(
    SELECT vehNo, tTime, odo,
            ROW_NUMBER() OVER (PARTITION BY vehNo, odo
                                ORDER BY tTime DESC) rn
    FROM    tableName
)
SELECT  vehNo, tTime, odo
FROM    latestDate
WHERE   rn = 1

Upvotes: 1

Rob
Rob

Reputation: 5588

I think you probably want to GROUP BY odo and select the MAX(tTime). Does that sound right?

Upvotes: 0

Related Questions