Jump to content


Photo

Select Max(date_tarif) Result Problem


  • Please log in to reply
17 replies to this topic

#1 violante

violante

    Member

  • Members
  • PipPip
  • 18 posts

Posted 11 August 2006 - 02:51 PM

: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

#2 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 8,148 posts
  • Gender:Male
  • Location:Skagen, Denmark

Posted 11 August 2006 - 03:38 PM

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):

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?
Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 TomBez

TomBez

    Advanced Member

  • Members
  • PipPipPip
  • 50 posts
  • Location:Austria

Posted 14 August 2006 - 08:32 AM

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.

#4 violante

violante

    Member

  • Members
  • PipPip
  • 18 posts

Posted 14 August 2006 - 11:18 AM

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:

#5 Bikas

Bikas

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 14 August 2006 - 11:23 AM

Try the following :

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

think it will work.

#6 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 8,148 posts
  • Gender:Male
  • Location:Skagen, Denmark

Posted 14 August 2006 - 01:00 PM

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.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#7 violante

violante

    Member

  • Members
  • PipPip
  • 18 posts

Posted 14 August 2006 - 01:21 PM

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


That's right it worked :D

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 :D

#8 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 8,148 posts
  • Gender:Male
  • Location:Skagen, Denmark

Posted 14 August 2006 - 01:32 PM

The order of ORDER BY and GROUP BY is wrong in your statement! GROUP BY comes first!
http://mysql.com/doc.../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!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#9 violante

violante

    Member

  • Members
  • PipPip
  • 18 posts

Posted 14 August 2006 - 02:41 PM

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]

* 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

#10 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 8,148 posts
  • Gender:Male
  • Location:Skagen, Denmark

Posted 14 August 2006 - 03: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.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#11 violante

violante

    Member

  • Members
  • PipPip
  • 18 posts

Posted 14 August 2006 - 03:37 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 :(

#12 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 8,148 posts
  • Gender:Male
  • Location:Skagen, Denmark

Posted 14 August 2006 - 03:56 PM

It must be then:

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:

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!

:)

Attached Files

  • Attached File  view.jpg   147.16KB   26 downloads

Computers make your grey hair come off ....

Peter Laursen
Webyog

#13 violante

violante

    Member

  • Members
  • PipPip
  • 18 posts

Posted 14 August 2006 - 04:25 PM

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

:blink: :(

Attached File  tar.zip   1.41KB   41 downloads
:o

#14 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 8,148 posts
  • Gender:Male
  • Location:Skagen, Denmark

Posted 14 August 2006 - 05:05 PM

Bikas was right all the time it seems!

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!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#15 TomBez

TomBez

    Advanced Member

  • Members
  • PipPipPip
  • 50 posts
  • Location:Austria

Posted 16 August 2006 - 08:36 AM

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.

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.




1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users