Postgresql I have a recent date inquiry question.

There are two tables.

/*article table*/
article_code / article_company / article_name / article_standard / article_unit / article_qauntity
"a004" "Sungsim Medical Device" "Disposable Injection" "15cc" 1 "BOX"
"a005" "Sungsim Medical Device" "Disposable Injection" "30cc" 1 "BOX"
"a006" "Sungsim Medical Device" "Disposable Injection" "50cc" 1 "BOX"
"b001" "International Culture Printing" "Application for Medical Treatment" "b5" 1 "Volume"
"b002" "International Culture Printing" "Application for Delivery" "a4" 1 "Volume"
"b003", "International Culture Printing", "Surgical Agreement", "a4" 1 "Volume"
"b004" "International Culture Printing" "A4" 1 "Volume"
"b005" "International Culture Printing" "Newborn Hearing Test"-"1 "EA"
"c001" "Chungcheongnam-do" "White Bundled Vinyl" "Medium" 1 "EA"
"c002" "Chungcheongnam-do" "White Bundled Vinyl" "Large" 1 "EA"
"c003", "Chungcheongnam-do", "Coffee", "100", 1 "BOX"
"a001", "Sungsim Medical Device", "Disposable Injector", "3cc" 100 "BOX"
"a002" "Sungsim Medical Device" "Disposable Injection" "5cc" 100 "BOX"
"a003" "Sungsim Medical Device" "Disposable Injection" "10cc" 100 "BOX"

/*articls_price*/
artilce_code / article_date / article_price
"a001"  "2019-02-21"    10000
"a001"  "2018-02-21"    9000
"a001"  "2017-02-21"    7000
"a002"  "2017-02-21"    10000
"a002"  "2019-02-21"    15000
"a003"  "2019-02-21"    50
"a004"  "2019-02-21"    70
"a005"  "2019-02-21"    80
"a002"  "2019-02-22"    9999

I want to reflect the article_price value as the most recent date, but I don't know how much.

SELECT articles.article_code, articles.article_company, articles.article_name, 
articles.article_standard, articles.article_unit, "article_quantity ", 
articles_price.article_date, articles_price.article_price
from articles
inner join articles_price 
on articles.article_code = articles_price.article_code
where articles_price.article_date=(
    select max(articles_price.article_date) 
    from articles_price
    group by articles.article_code
)
order by articles_price.article_price desc

Make the same code with join and check the date with the latest value

#Price value based on the latest date for each desired code
"a001" "Sungsim Medical Device" "Disposable Injection" "3cc" 100 "BOX" 10000 # 2019-02-21
"a002" "Sungsim Medical Device" "Disposable Injection" "5cc" 100 "BOX" 9999 # 2019-02-22
"a003" "Sungsim Medical Device" "Disposable Injector" "10cc" 100 "BOX" 9999 # 2019-02-21
...
..
.

I want to check it like this, but when I use the max function, only the a002 code comes out ㅜ 조언 Please give me some advice.

And the most curious thing is, why can't you check the "article_quantity" part of the "article_quantity""? I don't understand if there should be a space at the back. The data type is character balancing.

sql postgresql

2022-09-22 18:08

1 Answers

where articles_price.article_date in(
    select max(articles_price.article_date) 
    from articles_price
    where articles.article_code = articles_price.article_code
)

I am modifying the sentence where, but I can't understand the quotation marks no matter how hard I try ㅜ<

2022-09-22 18:08

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656