Click here to Skip to main content
12,633,764 members (31,595 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
Hi,

the SQL,

SELECT DATE,VRS_DEVICEID,LOCATION FROM 
(
SELECT  VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' AS LOCATION,
SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) AS DATE
FROM STS_VEHICLE_RUNNING_STATUS )AS T1

GROUP BY DATE,
VRS_DEVICEID,LOCATION ORDER BY DATE ASC



retrieve data as below

DATE          VRS_DEVICEID    LOCATION
      ----          ------------    --------

 2012-07-02 10:59       ST001        TVM
 2012-07-02 10:59       ST001        EKM
 2012-07-02 10:59       ST001        ATTIGAL
 
 2012-07-02 11:00       ST001        KOTTAKKAL
 2012-07-02 11:00       ST001        KARAMANA
 
 2012-07-02 11:02       ST001        MANNARKKAD
 2012-07-02 11:02       ST001        PALAKKAD


But i want to retrieve it as below

DATE          VRS_DEVICEID    LOCATION
      ----          ------------    --------

 2012-07-02 10:59       ST001        TVM
  
 2012-07-02 11:00       ST001        KOTTAKKAL
 
 2012-07-02 11:02       ST001        MANNARKKAD


That is, the top row of each DATE.

Thanks..
Posted 22-Nov-12 0:37am
hasbina1.4K
Updated 22-Nov-12 0:39am
__TR__29K
v2
Comments
digimanus 22-Nov-12 6:07am
   
how can you have the same ST001 ID at 3 different locations?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

SELECT DATE,VRS_DEVICEID,LOCATION FROM
(
    SELECT Row_number() over (
        PARTITION BY SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) order by VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' ) as rowno,
        VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' AS LOCATION,
    SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) AS DATE
    FROM STS_VEHICLE_RUNNING_STATUS
)AS T1
where rowno = 1
ORDER BY DATE ASC
  Permalink  
v5
Comments
hasbina 22-Nov-12 5:55am
   
@digimanus,
the SQL executed with error
Incorrect syntax near 'partion'.
digimanus 22-Nov-12 5:59am
   
sorry you are right it is the word partition
hasbina 22-Nov-12 5:59am
   
k.executed successfully.correction PARTITION BY.

But not get the desired result.

why?

Thanks..
digimanus 22-Nov-12 5:59am
   
which result do you get?
digimanus 22-Nov-12 6:01am
   
changed again. replaced the lat/lon combination with VRS_DEVICEID, cause that is what you are requesting in the main select, not the global position
hasbina 22-Nov-12 6:06am
   
@digimanus,

sir SQL,


SELECT DATE,VRS_DEVICEID,LOCATION FROM
(
SELECT Row_number() over (
PARTITION BY SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16)
order by VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')'
) as rowno,
VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' AS LOCATION,
SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) AS DATE
FROM STS_VEHICLE_RUNNING_STATUS
)AS T1
where rowno = 1
ORDER BY DATE ASC


give the desired result.


Thanks.
digimanus 22-Nov-12 6:09am
   
Great your problem is solved. If you accept my solution as answer this thread gets out of the unanswered list.
hasbina 22-Nov-12 6:18am
   
sir,

need change at PARTITION BY and ORDER BY in your solution as i write in above comment.
hasbina 22-Nov-12 6:13am
   
@digimanus

Thank you soo much sir..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 23 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100