How do i query large datasets based on value of one column with several columns that differ on only two columns
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
@Mustapha-Aliyu You haven't mentioned which database you are using! Is it Mysql or Access or something else?
I am using MS Access Database
@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!