ZLHysong ZLHysong - 1 month ago 7
MySQL Question

Need a certain Mysql Query Selection

I currently have a MYSQL Query as follows:

SELECT * FROM quotenumberlogbob
WHERE LEFT(`quotenumber`, 2) = '$year'
AND revision = 0
ORDER BY quotenumber DESC


$year
is a variable filled by the PHP file.

The table structure is as follows:

CREATE TABLE quotenumberlogbob(
id INT(10) unsigned NOT NULL AUTO_INCREMENT,
quoteNumber VARCHAR(255),
date VARCHAR(255),
buildingType VARCHAR(255),
Amount VARCHAR(255),
contactName VARCHAR(255),
repName VARCHAR(255),
company VARCHAR(255),
lengthft VARCHAR(255),
widthft VARCHAR(255),
heightft VARCHAR(255),
revision VARCHAR(255),
status VARCHAR(255) NOT NULL,
lengthin VARCHAR(255),
widthin VARCHAR(255),
heightin VARCHAR(255),
year VARCHAR(255),
CONSTRAINT `PRIMARY` PRIMARY KEY (id, status)
);


What I am trying to accomplish is to show only the highest 'revision' for each 'quotenumber'.

Where an example table might be:


| ID | quotenumber | revision | contactName | Price | Etc |
|-------|-------------|----------|-------------|-------|-----|
| 135 | 1609-4568 | 0 | Joe | $1350 | |
| 136 | 1609-4568 | 1 | Joe | $1900 | |
| 137 | 1609-4568 | 2 | Joe | $2650 | |
| 138 | 1609-4569 | 0 | Mike | $1500 | |
| 139 | 1609-4570 | 0 | Steve | $1600 | |
| 140 | 1609-4570 | 1 | Steve | $1900 | |
| 141 | 1609-4571 | 0 | Zack | $1590 | |
| 142 | 1609-4572 | 0 | Bob | $6050 | |


What I want to see is:


| ID | quotenumber | revision | contactName | Price | Etc |
|-------|-------------|----------|-------------|-------|-----|
| 137 | 1609-4568 | 2 | Joe | $2650 | |
| 138 | 1609-4569 | 0 | Mike | $1500 | |
| 140 | 1609-4570 | 1 | Steve | $1900 | |
| 141 | 1609-4571 | 0 | Zack | $1590 | |
| 142 | 1609-4572 | 0 | Bob | $6050 | |


Something along the lines of:

SELECT * FROM quotenumberlogbob
WHERE LEFT(`quotenumber`, 2) = '$year'
AND revision = max(revision)
ORDER BY quotenumber DESC


But obviously this doesn't work.

I know I can use PHP Loops to basically do the following, but I am trying to avoid that:

SELECT DISTINCT quotenumber
FOR EACH quotenumber
SELECT max(revision) WHERE quotenumber = $quoteNumberFromAbove
SELECT * FROM table WHERE quotenumber = $quoteNumber AND revision = $revision


I feel that this can all be done at once, but I am not experienced enough to know how.

If there is a simple way to do this, please let me know.

Edit: Mike has provided the answer to my question, but I feel it might help to add that here as well.

The query that was needed is:

SELECT *
FROM quotenumberlogbob
WHERE (quotenumber,revision)
IN(
SELECT quotenumber, max(revision)
FROM quotenumberlogbob
WHERE LEFT(`quotenumber`, 2) = '$year'
GROUP BY quotenumber
)
ORDER BY quotenumber DESC

Answer
SELECT *
  FROM quotenumberlogbob 
 WHERE (quotenumber,revision)
       IN(
          SELECT quotenumber, max(revision)
            FROM quotenumberlogbob 
           WHERE LEFT(`quotenumber`, 2) = '$year' 
           GROUP BY quotenumber
         )
 ORDER BY quotenumber DESC