SEO

#1 (**permalink**) 03-17-2008, 06:13 AM

Hi there,
I am banging my head against the wall because i cannot figure out
what select statement to use in the following case (or if it is even
possible ?!)

I have a table called "buchungen", and the columns "rezeptur_id" (int) und "datum"(date)
for each "rezeptur_id" there are a variable count of rows, all of which have a
different "datum" (means date)

My goal is to select the newest row for not only one "rezeptur_id", but fora list of different
"rezeptur_id"s.

The query

"select datum from buchungen where rezeptur_id=10 order by datum desc limit 1;"

would give me the latest row for "rezeptur_id" 10 (by the way, can this be done another way ? max(datum)
doesn't seem to work properly here ?!?)

So the question is, how can i retrieve this newest row for a list of "rezeptur_id"s ?

"select datum from buchungen where rezeptur_id in (10,20,30) order by datumdesc limit 1;

is obviously the wrong approac

Thank you for reading...

--
Greetz, lunqual - http://www.lunqual.de
http://www.42pixels.de - Bilder
http://www.rezeptbuch-pro.de - Software für den Destillateur

#2 (**permalink**) 03-17-2008, 06:13 AM

Karlheinz Klingbeil:

> So the question is, how can i retrieve this newest row for a list of "rezeptur_id"s ?
>
> "select datum from buchungen where rezeptur_id in (10,20,30) order by datum desc limit 1;

Not tested:

select max(datum)
from buchungen
where rezeptur_id in (10,20,30)
group by rezeptur_id
order by rezeptur_id

--
Erick

#3 (**permalink**) 03-17-2008, 06:13 AM

On Fri, 14 Mar 2008 12:58:09 +0100, Karlheinz Klingbeil
<karlheinz.klingbeil@gmx.net> wrote:

> Hi there,
> I am banging my head against the wall because i cannot figure out
> what select statement to use in the following case (or if it is even
> possible ?!)
>
> I have a table called "buchungen", and the columns "rezeptur_id" (int)
> und "datum"(date)
> for each "rezeptur_id" there are a variable count of rows, all of which
> have a
> different "datum" (means date)
>
> My goal is to select the newest row for not only one "rezeptur_id", but
> for a list of different
> "rezeptur_id"s.
>
> The query
>
> "select datum from buchungen where rezeptur_id=10 order by datum desc
> limit 1;"
>
> would give me the latest row for "rezeptur_id" 10 (by the way, can this
> be done another way ? max(datum)
> doesn't seem to work properly here ?!?)

Sure it is:
SELECT MAX(datum)
FROM buchungen
WHERE rezeptur_id = 10
GROUP BY rezeptur_id;

Should work, as long as your datum column is a proper date.

> So the question is, how can i retrieve this newest row for a list of
> "rezeptur_id"s ?
>
> "select datum from buchungen where rezeptur_id in (10,20,30) order by
> datum desc limit 1;

Either:
SELECT rezeptur_id,MAX(datum)
FROM buchungen
WHERE rezeptur_id IN (10,20,30)
GROUP BY rezeptur_id;

Or:
SELECT a.rezeptur_id, a.datum
FROM buchungen a
LEFT JOIN buchungen b
ON b.rezeptur_id = a.rezeptur_id
AND b.datum > a.datum
WHERE b.rezeptur_id IS NULL
AND a.rezeptur_id IN(10,20,30);
.... but that's just my LEFT JOIN fetish, pay no attention to that.
--
Rik Wasmus

#4 (**permalink**) 03-17-2008, 06:13 AM

Erick T. Barkhuis schreef:
> Karlheinz Klingbeil:
>
>> So the question is, how can i retrieve this newest row for a list of "rezeptur_id"s ?
>>
>> "select datum from buchungen where rezeptur_id in (10,20,30) order by datum desc limit 1;
>
> Not tested:
>
> select max(datum)
> from buchungen
> where rezeptur_id in (10,20,30)
> group by rezeptur_id
> order by rezeptur_id
>

select rezeptur_id, max(datum)
from buchungen
group by rezeptur_id
order by rezeptur_id

it think OP needs this, because he wrote:
"My goal is to select the newest row for not only one "rezeptur_id", but
for a list of different "rezeptur_id"s."...

--
Luuk

#5 (**permalink**) 03-17-2008, 06:13 AM

On Fri, 14 Mar 2008 12:58:09 +0100
Karlheinz Klingbeil <karlheinz.klingbeil@gmx.net> schrub:

Thank you all for the quick answer.

It's easy when you kno how to do it

--
Greetz, lunqual - http://www.lunqual.de
http://www.42pixels.de - Bilder
http://www.rezeptbuch-pro.de - Software für den Destillateur

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Need Help with select