How do i query large datasets based on value of one column with several columns that differ on only two columns

  • Hello,
    I have a query that returns data that is similar but differ in one row. I could not post a picture to show what I mean

    houseid tenantnumber payscale flatnumber roomno
    1 001 3 4 1
    1 002 2 4 1
    2 003 4 5 1
    2 004 4 5 1

    *sorry the spaces between the numbers indicate what column they belong to

    in the first two rows, their payscal is different. I want the person with the highest pay scale to be returned in my query while in the last two rows to return any of the tenants since their payscale is the same. Thank you

  • administrators

    @Mustapha-Aliyu You haven't mentioned which database you are using! Is it Mysql or Access or something else?

  • Apologies
    I am using MS Access Database

  • administrators

    @Mustapha-Aliyu This query should work

    SELECT Table1.houseid, Table1.flatnumber, Table1.roomnumber, Max(Table1.payscale) AS [Max Of payscale]
    FROM Table1
    GROUP BY Table1.houseid, Table1.flatnumber, Table1.roomnumber;

    But It won't return tenant number. I'm trying to get that too. I'll let you know if I could get it!

Log in to reply

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.