CODEWITHSUNDEEP
phpmysqlselect
user2635961
user2635961

Reputation: 379

Select within select for MYSQL

I am trying to SELECT specific lines of data from a MYSQL database

The database is as follows

Count   id     Startstation      stp
1      A1234   Nottingham         o
2      A1234   Nottingham         p
3      B2345   Nottingham         p
4      C6789   Leeds              o

I would like to select all the Nottingham stations but not repeat the same id. Also an stp code of o takes priority over an stp of p.

So I am looking for an output as follows:

A1234   Nottingham         o
B2345   Nottingham         p

Here is the code I have been trying to use but I cannot get it to work

require('connect_db.php');
mysqli_select_db($mysql_link,"timetable");               
$q="select * from (SELECT id,startstation, stp, ROW_NUMBER() OVER (PARTITION BY id ORDER BY stp  DESC) rnk FROM railtest WHERE startstation="Nottingham") where rnk = 1";

$r=mysqli_query($mysql_link,$q);
if ($r)
{                                       
while ($row=mysqli_fetch_array($r,MYSQLI_ASSOC))
{
echo $row['startstation'] . " " . $row['id']." ".$row['stp'];
echo "<br>";                
}               
}
else {echo '<p>'.mysqli_error($mysql_link).'</p>' ;}                        
mysqli_close($mysql_link);

I am getting a Parse error with my select statement

Hope some one can help

Thanks

Upvotes: 1

Views: 101

Answers (2)

Suvash sarker
Suvash sarker

Reputation: 3170

SELECT `id`,`startstation`, `stp`
FROM `railtest`
WHERE `startstation`='Nottingham'
GROUP BY `id`
ORDER BY `stp` ASC

Upvotes: 0

Jonathan Kuhn
Jonathan Kuhn

Reputation: 15301

SELECT `id`,`startstation`,min(`stp`) as stp
FROM `railtest`
WHERE `startstation`='Nottingham'
GROUP BY `id`, `startstation`

Group on the two columns that are the same and since o comes before p you can just select the min value. Obviously this works when there are two values for stp, more and you can put them alphabetically by priority or use a correlated sub-query to get the value for stp.

Upvotes: 1

Related Questions