select the last record group by? ident....

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

General SQL Server Forums

New to SQL Server Programming

select the last record group by? ident....

Author

Topic

Filaci
Starting Member

2 Posts

Posted - 2007-05-13 : 16:41:09

Hello

I have a table from cars GPS positions:
CREATE TABLE Positions (
Ident VARCHAR(20) NOT NULL,
Valid BOOL,
Date DATETIME NOT NULL,
Latitude FLOAT,
Longitude FLOAT,
Speed INT
}

Sorry about the poor english... :)
I like select the last position where Ident=DEMO1 and DEMO2....
How can I make that?
Practically I sould like to now, where is the last positions of selected cars!
I try this, but not good:
SELECT
MAX(date),
ident,
latitude,
longitude,
speed
FROM
positions
WHERE
valid=1 &&
(ident='DEMO1' || ident='DEMO2'|| ident='DEMO3')
GROUP BY
ident
;

I get 3 record, the dates is the last, but the lat. and long. is wrong...

Thanks,
Laci

Kristen
Test

22859 Posts

Posted - 2007-05-13 : 16:46:52

Perhaps this is what you are after?


SELECT	[date],
	P.ident,
	latitude,
	longitude,
	speed
FROM	positions AS P
	JOIN
	(
		SELECT	ident,
			MAX(date) AS [MAX_Date]
		FROM	positions
		WHERE	    valid=1
			AND ident IN ('DEMO1', 'DEMO2', 'DEMO3')
		GROUP BY ident
	) AS X
		 ON X.ident = P.ident
		AND X.MAX_Date = P.[date]
WHERE	    valid=1
	AND P.ident IN ('DEMO1', 'DEMO2', 'DEMO3')

Kristen

Filaci
Starting Member

2 Posts

Posted - 2007-05-13 : 17:18:18

Thanks for quick answer!

The subselect work perfectly, but the entire select has 0 rows result.

I now use this solution, but in PHP must execute in loop, many times as idents:
(I use ident translation, the user view the licence plate number, not the ident...)
foreach($_GET as $rsz)
{
$eredmeny = mysql_query('
SELECT
datum, //date
poziciok.keszulekaz, //ident
szelesseg, //lat
hosszusag, //long
bemenet1,
elnevezes //name
FROM
poziciok //positions
LEFT JOIN
elnevezesek //names
ON
elnevezesek.keszulekaz = poziciok.keszulekaz AND elnevezesek.felhasznalonev = \''.$_SESSION['LoginNev'].'\' //username

WHERE
poziciok.keszulekaz = \''.$rsz.'\' &&
valid = 1
ORDER BY
datum DESC
LIMIT
0,1
');
.
.//mysql_fetch_array...
.
}
This solution is very slow....

Laci

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 02:15:03

This forum is for Microsoft SQL Server questions.
PHP and/or MySQL questions are better answered at www.dbforums.com

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-05-14 : 02:17:54

Does MySql even support a nested sub-select like that these days?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-14 : 03:13:32

or post at http://forums.mysql.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources