Select Max(date_tarif) Result Problem

[violante]

:huh:

Hi, i have the following table:

MAGASIN ARTICLE_NT DATE_TARIF MARGE_THEO

1 76608 16-08-2001 0

1 76608 18-12-2001 17,58

1 76608 10-01-2002 77,9

1 76608 02-05-2006 67,64

What I want is to obtain the most recent MARGE_THEO value.

with the following statement:

select MAGASIN,ARTICLE_NT, MAX(DATE_TARIF) AS DATE_TARIF, MARGE_THEO FROM TAR

GROUP BY ARTICLE_NT

the result is:

MAGASIN ARTICLE_NT DATE_TARIF MARGE_THEO

1 76608 02-05-2006 0

The DATE_TARIF is correct but MARGE_THEO value is the first row Value and not the one from the 02-05-2006 date

could anyone help me in this importation???

Thanks

[peterlaursen]

I am not sure I understand!

First an EXACT 'create statement' would always make things easier! It is not clear what data types you use – and therefor not what is the correct sorting order either. Your dataes look weird with DD-MM-YYY (or MM-DD-YYYY ?) and not YYYY-MM-DD as would be normal with MySQl

I would propose simply (provided that I understand):

Code:

select MAGASIN,ARTICLE_NT, DATE_TARIF, marge_theo FROM tar order by marge_theo desc limit 1;

But do you want to retrieve the max. value for date_tarif and at the same time the max. value for marge_theo (not corresponding values)?

Please:

1) the 'create statement for the table' (or better a complete dump with structure + data)

2) what exactly should be returned?

[TomBez]

This is because you select not a certain row (the row where the value of max(column) occures you should select), therefore he takes the value of the first row and the value for max(column) from the row where it occures.

[violante]

peterlaursen wrote on Aug 11 2006, 04:38 PM:

But do you want to retrieve the max. value for date_tarif and at the same time the max. value for marge_theo (not corresponding values)?

No, what Iwould like is the max. value for date_tarif and the marge_theo value of the row that corresponds to max. value for date_tarif

Do you understand what i need??

Sorry but my english is not verry goos :huh:

[Bikas]

Try the following :

select * from tar where date_tarif in (select max(date_tarif) from tar group by article_nt);

think it will work.

[peterlaursen]

Quote:

No, what Iwould like is the max. value for date_tarif and the marge_theo value of the row that corresponds to max. value for date_tarif

wel then I think that my first 'shot' will work!

Select * from tar ORDER BY date_tarif desc limit 1

.. will sort the rows in descending order by date_tarif and only 1 row will be shown – the one with the higest value of data_tarif. I see no reason to use aggregate functions ( max() ) here! Doing so will put an additional load on the server as the optimizer rewrites the query internally and probably needs to build temporary tables.

[violante]

Quote:

wel then I think that my first 'shot' will work!

That's right it worked 😀

But my table has multipli values for article_nt…

I would like to have it grouped by article_nt …

I tried:

Select * from tar

ORDER BY date_tarif desc limit 1

group by article_nt

And happened the following error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by article_nt' at line 3

May be the 2nd shot will end with my problem 😀

[peterlaursen]

The order of ORDER BY and GROUP BY is wrong in your statement! GROUP BY comes first!

http://mysql.com/doc/refman/5.0/en/select.html

I do not understand what you mean by: “I would like to have it grouped by article_nt …” There is only ONE row with the date_tarif max-value, so I do not understand what to GROUP.

Please help us to understand:

* DUMP/export the table so that we have EXACTLY the same – the same data types etc.

* Write the output here EXACTLY and COMPLETELY as you want it!

[violante]

Quote:

EXACTLY the same – the same data types etc.

Date types:

MAGASIN [smallint(1), NOTNULL]

ARTICLE_NT [smallint(5), NOTNULL]

DATE_TARIF [date, NOTNULL]

MARGE_THEO [decimal(6,2), null]

PA_BRUT [decimal(9,3),NULL]

PV_CONSO[decimal(9,3),NULL]

Quote:

* Write the output here EXACTLY and COMPLETELY as you want it!

MAGASIN

---

ARTICLE_NT

---

DATE_TARIF

---

MARGE_THEO

1

---

76608

---

16-08-2001

---

0

1

---

76608

---

18-12-2001

---

17,58

1

---

76608

---

10-01-2002

---

77,9

1

---

76609

---

02-01-2006

---

67,64

1

---

76609

---

10-02-2006

---

34,9

1

---

76610

---

01-05-2006

---

67,64

1

---

76610

---

02-05-2006

---

48,9

1

---

76610

---

03-05-2006

---

59,83

The output should be:

MAGASIN

---

ARTICLE_NT

---

DATE_TARIF

---

MARGE_THEO

1

---

76608

---

10-01-2002

---

77,9

1

---

76609

---

10-02-2006

---

34,9

1

---

76610

---

03-05-2006

---

59,83

[peterlaursen]

@violante

next time please post a COMPLETE DUMP ready for import! Why should I waste time TYPING when I could easily import your DUMP in a seconds.

[violante]

peterlaursen wrote on Aug 14 2006, 04:27 PM:

@violante

next time please post a COMPLETE DUMP ready for import! Why should I waste time TYPING when I could easily import your DUMP in a seconds.

🙁 sorry… 🙁

do you want me to export it as CSV???

I'm quite new with this, sorry 🙁

[peterlaursen]

It must be then:

Code:

select magasin,article_nt, max(date_tarif) as date_tarif, marge_theo from tar group by article_nt order by date_tarif asc

'group by article_nt ' assures that only ONE row with identical article_nt value is returned

'max(date_tarif) as date_tarif' assures that this ONE row will be the one with the biggest value for data-tarif

' order by date_tarif ' specifies in what order the ONE row with max(date_tarif) for each existing value for article_nt is displayed

Actually I have a VIEW definition quite similar:

Code:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `albumtitel` AS select
count(*) AS `##`,`mp3_filer`.
`Albumtitel` AS `Albumtitel`,`mp3_filer`.
`Genre` AS `Genre`,`mp3_filer`.
`Kunstner` AS `Kunstner` from `mp3_filer`
where ((`mp3_filer`.`Kunstner` is not null) and (`mp3_filer`.`Albumtitel` is not null))
group by `mp3_filer`.`Albumtitel`,`mp3_filer`.`Kunstner`,`mp3_filer`.`Genre`
order by `mp3_filer`.`Albumtitel`,`mp3_filer`.`Kunstner`,`mp3_filer`.`Genre`

It builds an 'overview' from my music library based in Album Title. See attached.

You should export the table as SQL! because SQL can be executed and every bit of definition and data is inserted in one single operation!

🙂

[violante]

It didn't worked 🙁

I hope you can import the table

The max(DATE_TARIF) value is correct, but MARGE_THEO value isn't correct

The out from your formula is:

MAGASIN

---

ARTICLE_NT

---

DATE_TARIF

---

MARGE_THEO

1

---

76608

---

2006-05-02

---

0.00

1

---

76609

---

2100-06-03

---

5.19

1

---

76610

---

2005-07-01

---

10.11

1

---

76611

---

2006-04-25

---

5.39

1

---

76612

---

2002-08-08

---

4.04

but the correct output should be:

MAGASIN

---

ARTICLE_NT

---

DATE_TARIF

---

MARGE_THEO

1

---

76608

---

2006-05-02

---

67.64

1

---

76609

---

2100-06-03

---

9.11

1

---

76610

---

2005-07-01

---

8.42

1

---

76611

---

2006-04-25

---

4.27

1

---

76612

---

2002-08-08

---

1.04

What Happens is that MARGE_THEO value doesn't correspond with the DATE_TARIF row

😮 🙁

[attachment=505:attachment]

😮

[peterlaursen]

Bikas was right all the time it seems!

Code:

select magasin,article_nt, date_tarif , marge_theo from tar where date_tarif in (select max(date_tarif) from tar group by article_nt);

Actually I think MySQL internally builds a temporary table to hold the values for the sub-select and next does a query with JOINs on both tables (yours and the temporary). But it is simpler than specifying the JOINs yourself!

The subselect simply returns:

max(date_tarif)

---

2006-05-02

2100-06-03

2005-07-01

2006-04-25

2002-08-08

But now .. you may have more records with the same value for date_tarif and article_nt! Considered how to handle this?

Your current Primary Key does not protect against this – it is defined as:

PRIMARY KEY (`MAGASIN`,`ARTICLE_NT`,`DATE_TARIF`)

so it is possble to create records with the same value for date_tarif and article_nt if only another column is different!

You could build a UNIQUE KEY with date_tarif and article_nt to prevent this!

[TomBez]

peterlaursen wrote on Aug 14 2006, 07:05 PM:

Code:

select magasin,article_nt, date_tarif , marge_theo from tar where date_tarif in (select max(date_tarif) from tar group by article_nt);

this can be very slow on large tables, avoiding “select .. from .. where … in (select)” is for larger tables very usefull especially if you can expect to get a lot of values from the “in (select)” or if you have an order by/group by in the “in(select)” statement.

peterlaursen wrote on Aug 14 2006, 07:05 PM:

PRIMARY KEY (`MAGASIN`,`ARTICLE_NT`,`DATE_TARIF`)

in my opinion primary keys should always be autoincrement values and never the natural key. if you need it, use a unique key or create a trigger for that. as the application might get older, you will maybe notice that your natural key isnt unique anymore and changes of the primary key do harm to the whole E/R-modell.

[peterlaursen]

It is true that subselects can be slow, if it results in large temporary tables.

However this temporary tables has only 5 rows!

I data shall be SYNC'ed with SQLyog it is not alwas a good idea to have a auto-icrement PK.

Even the 'smartest' database experts disagree on that!

I think you coul JOIN the table to itself as well.

One day when I get the time I will study joins in details!

I think the issue here is the SCHEMA itself. When there is so much troubl with a query it often indicates that the SCHEMA design is not optimal! I think it was an option to have data in two InnoDB tables related with Foreign Keys – but I do not fully understand the data.

The reason the my proposal did not work was because of the max() aggregate function. Int would have worked with count().

[TomBez]

peterlaursen wrote on Aug 16 2006, 12:08 PM:

I data shall be SYNC'ed with SQLyog it is not alwas a good idea to have a auto-icrement PK.

Even the 'smartest' database experts disagree on that!

Maybe it is because I had to often build a lot of functionality around a natural key which didn't worked as designed, sometimes the real world changes, so that adoptions in den schema itself have to made, with surrugate keys less of them have to be made, but thats only my experience.

peterlaursen wrote on Aug 16 2006, 12:08 PM:

I think you coul JOIN the table to itself as well.

One day when I get the time I will study joins in details!

I'm not sure if mysql supports selfreferencing like oracle. i haven't found any “connect by” clause in the documentation. a statement like “select mytable1.* from mytable mytable1, mytable mytable2 where mytable1.column1>10 and mytable2.column1<3" is possible and works

[Author]

Posts