need help with this query statement...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need help with this query statement...

Author

Topic

xwarrior
Starting Member

7 Posts

Posted - 2006-12-06 : 22:30:45

i have 4 fields, TagID, x,y,Tdate.
tag1, 123,523, date
tag1, 123, 123, latest date
tag2, 434,423, latest date
tag2, 123,432, date
tag3,342, 543,date
tag3,532,342,latest date

in the TagID field i have different names. i want to use a query statement to draw the x and Y of each different TagID by the latest TDate.

this is my query statement

Code:
SELECT
DISTINCT
TagID,
x,
y
from RTLOC
where TDate =
(SELECT max(TDate) from RTLOC)the result given back is only 1 TagID.

expected result should be TagID , x , y , TDate
tag1 ,123 ,123 ,latest date
tag2 ,434 ,423 ,latest date
tag3 ,532, 342 , latest date

can this be done? thanks for your help.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 00:04:51

Something like this?

SELECT 
TagID,
x,
y,
max(TDate) as TDate
from RTLOC
Group by TagID,
x,
y

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-07 : 00:12:47

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

xwarrior
Starting Member

7 Posts

Posted - 2006-12-07 : 00:26:09

it should be something like this

SELECT
DISTINCT
TagID,
x,
y
from RTLOC
where TDate =
(SELECT max(my.TDate) from RTLOC as my
and TagID = my.TagID)

but this is in MYSql format. anybody can change it to sql server 2005 for me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 01:45:48

This should do it

SELECT DISTINCT	q.TagID,
		q.x,
		q.y,
		q.TDate
from		RTLOC AS q
where		q.TDate = (SELECT MAX(w.TDate) from RTLOC AS w)

Peter Larsson
Helsingborg, Sweden

xwarrior
Starting Member

7 Posts

Posted - 2006-12-07 : 01:52:46

the result only me 1 tag. i need the result to display the different tags' x and y based on their latest date.

xwarrior
Starting Member

7 Posts

Posted - 2006-12-07 : 01:53:09

the result only return me 1 tag. i need the result to display the different tags' x and y based on their latest date.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 01:59:47

Unless you give us some PROPER sample data for dates, all we can give is suggestions based on advanced GUESSES!
Do the datetime column has time information too?

Try this

SELECT DISTINCT	q.TagID,
		q.x,
		q.y,
		q.TDate
from		RTLOC AS q
where		q.TDate >= (SELECT MAX(DATEADD(day, DATEDIFF(day, 0, w.TDate), 0)) from RTLOC AS w)

Peter Larsson
Helsingborg, Sweden

xwarrior
Starting Member

7 Posts

Posted - 2006-12-07 : 02:09:31

my TDate is in this format--> 11/28/2006 10:04:34 AM.

distinct doesn't seem to be working

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 02:32:22

[code]SELECT r.TagID,
r.x,
r.y,
r.TDate
FROM (
SELECT TagID,
MAX(TDate) peso
FROM RTLOC
GROUP BY TagID
) d
INNER JOIN RTLOC r ON r.TagID = d.TagID AND r.TDate = d.peso[/code]

Peter Larsson
Helsingborg, Sweden

xwarrior
Starting Member

7 Posts

Posted - 2006-12-07 : 02:50:18

there is a syntax errror( missing operator in query expressin MAX(TDate) peso

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 02:55:12

Are you doing this from MS-Access?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 02:58:57

Not if you are using SQL Server!

SELECT		r.TagID,
		r.x,
		r.y,
		r.TDate
FROM		(
			SELECT		TagID,
					MAX(TDate) AS peso
			FROM		RTLOC
			GROUP BY	TagID
		) d
INNER JOIN	RTLOC r ON r.TagID = d.TagID AND r.TDate = d.peso

Peter Larsson
Helsingborg, Sweden

xwarrior
Starting Member

7 Posts

Posted - 2006-12-07 : 03:19:22

thanks. it is solved now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:38:01

Which DBMS are you using?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:42:11

If you are using MS ACCESS, please post future questions regarding MS ACCESS here
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-07 : 05:07:35

It is always preferrable to use AS to name alias name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources