mercredi 20 septembre 2017

How to compare a column value in one row to column value in another row in postgresql

I'm working with Postgresql 8.0. I have a table as follows:

Table1:

ID  | Val | Num | Val2

ABC | High | 22 | Low
ABC | Low  | 2  | High
ABC | High | 16 | Low
DFG | High | 10 | High
DFG | High | 50 | High
DFG | Low  | 3  | High

How can I get an output where for the same IDs in the first column, it checks the Val column, gives high precedence to 'High' value than 'Low' or 'Mod' and then from among the rows for that ID with 'High' in the Val column select the row with higher value in the 'Num' column. For the above sample the output should be as follows:

ID | Val | Num | Val2

ABC | High | 22 | Low
DFG | High | 50 | High

Can someone guide me how to achieve this? Can I use case statement for this??

Aucun commentaire:

Enregistrer un commentaire