Query Help

[computermax2328]

Hello all,

 

I am working on this query to show past records of data for all of the records in this particular table. This is an archive table so there are multiple entries of the same person in the database. The difference is the date that they were entered, which is an auto entry value added on the date it is uploaded. Here is the query:

 

 

"SELECT politicians.id, politicians.`Last Name`, politicians.`First Name`, politicians.`District`, archive.`firstname`, archive.`lastname`, archive.rank, archive.era FROM politicians, archive WHERE politicians.`Last Name`=archive.lastname AND politicians.`First Name`=archive.firstname ORDER BY date LIMIT 150";

 

The problem is that I only need the latest entry from the persons information and I am getting multiple from past dates. They need to be ordered by their past rank, but from the last date of the entry.

 

Any ideas??

 

P.S I know I already posted this in the applications section. I posted it there by accident

[Barand]

which table contains the date?

[computermax2328]

The archive table, but the first and last name for both tables are the key between the two. I need some information from the other table.

[Barand]

Use a subquery to get latest date for each and match against that.

 

There is an example here http://forums.phpfreaks.com/topic/273237-calculating-total-of-valuations-based-on-varying-percentage-against-a-sum/?do=findComment&comment=1406193

[computermax2328]

Hey,

 

So I am coming back because I tried the subquery and it did not work.

 

To go over the details of my problem again, this is what is going on. I have an archive database with multiple entries of the same value in the database, Example:

 

Joe 1

Joe 2

Sarah 1

Sarah 3

 

The entries are dated by the date that they were entered. This what I have found out so far. I used SELECT DISTINCT in order to eliminate any other names. Query follows:

 

$sql = "SELECT DISTINCT firstname, lastname FROM archive ORDER BY rank";

 

So now I am getting all of the name indivually, just Joe and just Sarah, but what I need is for Joe and Sarah to have the ability to have the same number next to their name, so Distinct name but not number. For example:

 

Joe 1

Sarah 1

John 2

Phil 3

Bob 3

 

Any ideas?

[Barand]

Hey,

 

So I am coming back because I tried the subquery and it did not work.

 

That's a pity because that's the way to do it.

 

What exactly did you try?

[computermax2328]

I did subqueires like:

 

$sql="SELECT firstname, lastname, rank FROM archive WHERE firstname=(SELECT 'First Name' FROM politicians  AND lastname=(SELECT 'Last Name' FROM politicians) ORDER BY rank";

 

I do have to get information from the politicians database as well but right now I am just working on getting the information from this one database. Again I need information like this.

 

Joe 1

Sarah 1

Steve 2

Phil 3

Alex 3

[Barand]

I did subqueires like:

 

$sql="SELECT firstname, lastname, rank FROM archive WHERE firstname=(SELECT 'First Name' FROM politicians AND lastname=(SELECT 'Last Name' FROM politicians) ORDER BY rank";

 

And in what way is that similar to the example I gave you?

[computermax2328]

I am new at this. I don't understand your example. Can you help me learn this??

[Barand]

The first thing to do is to find the latest archive date for each name.

 

SELECT firstname, lastname, MAX(date) as date
FROM archive
GROUP BY firstname, lastname

 

We will use the above as a table subquery. This behaves as though you have a table containing three columns (firstname, lastname, latestdate) so when we INNER JOIN it with the archive table we only get those archive records where these three fields match, ie the lates archive record for each name.

 

As we are using this subquery as a logical table we have to give it an alias, in this case "latest"

 

So our query becomes

 

SELECT politicians.id, politicians.`Last Name`,
   politicians.`First Name`, politicians.`District`,
   archive.`firstname`, archive.`lastname`,
   archive.rank, archive.era
FROM politicians
   INNER JOIN archive
    ON politicians.`Last Name`=archive.lastname
	    AND politicians.`First Name`=archive.firstname
   INNER JOIN
    (
    SELECT firstname, lastname, MAX(date) as date
    FROM archive
    GROUP BY firstname, lastname
    ) as latest
    ON archive.firstname=latest.firstname
	    AND archive.lastname=latest.lastname
	    AND archive.date=latest.date	    
ORDER BY date LIMIT 150;

[Barand]

PS You should not be using names to match records in tables, You should store the unique id of the person in the archive and match on that. It is quite possible for two or more people to have the same name.

[computermax2328]

Thanks that helped alot. Yes, I understand that you are suppose to store a unique ID, but I was unaware of this when I first created this particular site. Something I do regret. Thanks again.