Menu
I have two mysql tables - a sales table:
+----------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------------------+------+-----+---------+-------+
| StoreId | bigint(20) unsigned | NO | PRI | NULL | |
| ItemId | bigint(20) unsigned | NO | | NULL | |
| SaleWeek | int(10) unsigned | NO | PRI | NULL | |
+----------------+------------------------------+------+-----+---------+-------+
and an items table:
+--------------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------------------+------+-----+---------+-------+
| ItemId | bigint(20) unsigned | NO | PRI | NULL | |
| ItemName | varchar(100) | NO | | NULL | |
+--------------------+------------------------------+------+-----+---------+-------+
The sales table contains multiple records for each ItemID - one for each SaleWeek. I want to select all items sold by joining the two tables like so:
SELECT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;
However, this is returning multiple ItemId values based on the multiple entries for each SaleWeek. Can I do a distinct select to only return one ItemID - I don't want to have to query for the latest SaleWeek because some items may not have an entry for the latest SaleWeek so I need to get the last sale. Do I need to specify DISTINCT or use a LEFT OUTER JOIN or something?
206
SQL DISTINCT clause is used to remove the duplicates columns from the result set. The distinct keyword is used with select keyword in conjunction. It is helpful when we avoid duplicate values present in the specific columns/tables. The unique values are fetched when we use the distinct keyword.
To get unique or distinct values of a column in MySQL Table, use the following SQL Query. SELECT DISTINCT(column_name) FROM your_table_name; You can select distinct values for one or more columns.
The SQL SELECT DISTINCT StatementThe SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.
A DISTINCT should do what you're looking for:
SELECT DISTINCT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;
That would return only distinct items.ItemName, items.ItemId tuples.
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
