Kindly help me in this simple query

Question

Post reply

Kindly help me in this simple query

cmrhema

SSCommitted

Points: 1558
More actions
October 17, 2008 at 4:33 am

#194700

Hi All
I know that this is a very simple query but somewhere struggling
These are the columns
gps_datetime registrationno, speed
10/10/2008 A123 50
11/10/2008 A123 30
12/10/2008 A123 20
01/10/2008 B123 50
02/10/2008 B123 30
03/10/2008 B123 20
04/10/2008 C123 50
05/10/2008 C123 30
06/10/2008 C123 20
07/10/2008 D123 50
08/10/2008 D123 30
09/10/2008 D123 20
Actually there will be more fields
All i want is to display all the fields with the maximum gps_datetime, the result should be as below
12/10/2008 A123 20
03/10/2008 B123 20
06/10/2008 C123 20
09/10/2008 D123 20
I gave the query as
SELECT REGISTRATIONNO, MAX(GPS_DATETIME) FROM gpsdata_history GROUP BY REGISTRATIONNO
This returned proper values, but when I included the rest of the fields as
select registrationno,speed MAX(GPS_DATETIME) FROM gpsdata_history GROUP BY REGISTRATIONNO
It returned all the values,
Kindly let me know how do I frame the query.
Regards
Hema

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

try something like this:

select gpsdata_history.*

from gpsdata_history

inner join

(SELECT

REGISTRATIONNO, MAX(GPS_DATETIME)

FROM gpsdata_history

GROUP BY REGISTRATIONNO

) X

ON gpsdata_history.REGISTRATIONNO = X.REGISTRATIONNO

AND gpsdata_history.GPS_DATETIME = X.GPS_DATETIME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

If you add more columns you need to expand your group by clause as well

select 1,2,3,4,5, max(6)

from table

group by 1,2,3,4,5

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

Hemalatha (10/17/2008)
Hi All
I know that this is a very simple query but somewhere struggling
These are the columns
gps_datetime registrationno, speed
10/10/2008 A123 50
11/10/2008 A123 30
12/10/2008 A123 20
01/10/2008 B123 50
02/10/2008 B123 30
03/10/2008 B123 20
04/10/2008 C123 50
05/10/2008 C123 30
06/10/2008 C123 20
07/10/2008 D123 50
08/10/2008 D123 30
09/10/2008 D123 20
Actually there will be more fields
All i want is to display all the fields with the maximum gps_datetime, the result should be as below
12/10/2008 A123 20
03/10/2008 B123 20
06/10/2008 C123 20
09/10/2008 D123 20
I gave the query as
SELECT REGISTRATIONNO, MAX(GPS_DATETIME) FROM gpsdata_history GROUP BY REGISTRATIONNO
This returned proper values, but when I included the rest of the fields as
select registrationno,speed MAX(GPS_DATETIME) FROM gpsdata_history GROUP BY REGISTRATIONNO
It returned all the values,
Kindly let me know how do I frame the query.
Regards
Hema

Select gps_datetime

,registrationno

,speed

From (

Select gps_datetime

,registrationno

,speed

,Row_number() Over(Partition By registrationno Order By gps_datetime Desc) As

rn

From gpsdata_history

) As x

Where rn = 1;

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

v.vinesh SSC-Addicted Points: 423 More actions · Answer 4

i think u can go with Lowells solution.(first reply for this thread)