distinct item for each date
distinct item for each date
for each date, i have different items with unique ID, how to create a table to show each date with distinct item in each day?
date |item | Trade ID
1 |A | 123
2 |A | 124
1 |A | 125
3 |B | 126
1 |A | 127
2 |A | 128
3 |C | 129
1 |A | 130
desired results
date |item
1 |A
2 |A
3 |B
3 |C
i tried the following code, but i got an error msg
select date, distinct item
from mytable
it says found "distinct" expecting an identifier found a keyword
thank you!
SELECT DISTINCT
Why is date 1 always combined with A? Is this a coincidence in your sample data? Can one date occur with two different items? If yes: what to do in this situation? If no: change your data model (i.e. normalize it).
– Thorsten Kettner
Jul 1 at 23:35
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql, oracle, sql-server, db2, ...– a_horse_with_no_name
Jul 2 at 7:18
postgresql
oracle
sql-server
db2
3 Answers
3
Try with DISTINCT like:
DISTINCT
SELECT DISTINCT
date,
item
FROM elbat;
Or with GROUP BY like:
GROUP BY
SELECT date,
item
FROM elbat
GROUP BY date,
item;
Why GROUP BY when no aggregate functions are involved?
– jarlh
Jul 2 at 6:57
DISTINCT doesn't work on certain columns, but on the whole row you are selecting. It has to follow the SELECT keyword directly.
DISTINCT
SELECT
select distinct date, item from mytable;
select distinct date, item
from your_table
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Hint:
SELECT DISTINCT.– Gordon Linoff
Jul 1 at 23:29