How to group by two rows in a column

by

Left join location table on data to get all locations regardless if there is match in data table, group by location id and location, and I assume you want to get the 1st name (min()):

select l.id, l.location, min(d.name) as name
from location l
left join data d on l.location=d.location
group by l.id, l.location

Alternatively, if you want to get for a data item if it has offshore and onshore record:

select d.id, l.location, d2.name
from location l
join data d
left join data d2 on l.location=d2.location
where d.id=1

More Related Contents:

  1. How to use IN & NOT IN same query [closed]
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. How to delete duplicates on a MySQL table?
  4. need to return two sets of data with two different where clauses
  5. quick selection of a random row from a large table in mysql
  6. Best data type to store money values in MySQL
  7. How to fix “Incorrect string value” errors?
  8. MySQL: How to allow remote connection to mysql
  9. What’s the difference between ” = null” and ” IS NULL”?
  10. Querying multiple databases at once
  11. Sorting varchar field numerically in MySQL
  12. SQL: Repeat a result row multiple times, and number the rows
  13. What MySQL data type should be used for Latitude/Longitude with 8 decimal places?
  14. Setting up foreign keys in phpMyAdmin?
  15. How to get an age from a D.O.B field in MySQL?
  16. Invalid default value for ‘dateAdded’
  17. Importing a CSV to MySQL with different date format
  18. MySQL: When is Flush Privileges in MySQL really needed?
  19. MySQL too long varchar truncation/error setting
  20. MySQL: update a field only if condition is met
  21. how to use DISTINCT ON with mysql using ActiveRecord
  22. Using version control (Git) on a MySQL database
  23. mySQL – Create a New Table Using Data and Columns from Three Tables
  24. use a variable for table name in mysql sproc
  25. How to create dummy variable columns for thousands of categories in Google BigQuery?
  26. Most efficient way to store IP Address in MySQL [duplicate]
  27. What is the best way to do incremental backups in MySQL?
  28. Which ORM should I use for Node.js and MySQL? [closed]
  29. What does it mean by select 1 from table?
  30. MySQL select all rows from last month until (now() – 1 month), for comparative purposes

Leave a Comment