CODEWITHSUNDEEP
sql
NullPointerException
NullPointerException

Reputation: 37701

SQL Query: Select most recent entry for each distinct field value?

i have this table POSITION: 

CREATE TABLE `position` (
  `idposition` bigint(20) NOT NULL AUTO_INCREMENT,
  `latitude` varchar(45) DEFAULT NULL,
  `longitude` varchar(45) DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  `fk_email` varchar(100) DEFAULT NULL,

with this population: 

idposition  latitude    longitude   timestamp              fk_email
        1   39.49333    -0.33667    2010-12-10 17:15:39     [email protected]
         4  39.47333    -0.36167    2010-12-11 09:58:47     [email protected]
         5  39.50333    -0.34267    2010-12-10 21:11:10     [email protected]
        10  39.44333    -0.41667    2010-12-12 18:17:26     [email protected]
        11  39.45199    -0.31967    2010-12-12 21:01:18     [email protected]
        12  39.52133    -0.36167    2010-12-12 17:43:11     [email protected]
        13  39.43199    -0.31967    2010-12-14 15:45:25     [email protected]
        14  39.41199    -0.34567    2010-12-14 20:46:09     [email protected]
        15  39.43433    -0.41667    2010-12-14 17:29:39     [email protected]
        16  39.44133    -0.42667    2010-12-12 19:17:33     [email protected]
        17  39.50333    -0.35667    2010-12-13 16:36:22     [email protected]
        196     39.46454    -0.38978    0000-00-00 00:00:00     [email protected]
        271     33.63883    -0.45024    2010-12-17 11:36:45     [email protected]
        273     39.46680    -0.39452    2010-12-20 00:00:00     [email protected]
        441     39.44133    -0.41467    2010-12-15 17:45:13     [email protected]
        5326    39.47316    -0.38351    2011-01-18 18:09:56     [email protected]

ok, i want to make SQL Query, that returns to me THE LAST (NEWEST) POSITION OF EACH FK_EMAIL, i mean, for each fk_email, the position with the last (newest) timestamp, only one position (te last) for each email.

it is possible to do it? can someone help me with the query? is too munch hard for me

Upvotes: 0

Views: 451

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52675

SELECT  
      p.idposition, 
      p.atitude, 
      p.longitude, 
      p.timestamp, 
      p.fk_email

FROM  
      position p 
      INNER JOIN (select 
              fk_email,
              max(timestamp) timestamp
                 FROM    POSITION 
                 GROUP BY  fk_email) lastPos 
      ON p.fk_email = lastPos.fk_email and p.timeStamp = lastpos.timestamp

The one potential problem with this is that if the same timestamp is recorded for the same fk_email you will get both results. If there was a unique constraint on the combination of these two columns you won't have to worry about that though.

update From the comments

but one question more, the only possibility to be returned two rows per email is to have the seame timestamp in two rows for the same email? how can i evade that

As I said before adding a unique constraint on the two fields so it doesn't happen is the easiest. But lets assume you can't do that and lets also assume that the timestamp is the same. you'll need to pick an arbitrary row as "the correct one".

Assuming that IdPosition is unique you could do this

SELECT 
     p.idposition, 
      p.atitude, 
      p.longitude, 
      p.timestamp, 
      p.fk_email
  FROM 
   position p
    INNER JOIN (SELECT  
                    max(p.idposition) idposition
              FROM  
                    position p 
                    INNER JOIN (select 
                            fk_email,
                            max(timestamp) timestamp
                               FROM    POSITION 
                               GROUP BY  fk_email) lastPos 
                    ON p.fk_email = lastPos.fk_email and p.timeStamp = lastpos.timestamp
              GROUP BY 
                     fk_email) lastpos
              on p.idposition = lastpos.idposition

Upvotes: 5

bensiu
bensiu

Reputation: 25604

SELECT idposition, atitude, longitude, timestamp, fk_email 
FROM   position 

GROUP BY fk_email HAVING MAX( timestamp )

Upvotes: 1

jlnorsworthy
jlnorsworthy

Reputation: 3974

Try this:

select * from
(select idposition, latitude, longitude, timestamp, fk_email, row_number() over(partition by fk_email order by timestamp desc) as rownumber
from position
) derived
where rownumber = 1

Upvotes: 4

Related Questions