SQL query question

Поиск
Список
Период
Сортировка
Искать

SQL query question

От:
"Alloni Kramer" <alloni@aposiopesis.net>
Дата:

Hi.  I've got a problem that I need to figure out, and I can't seem to do
it with the references I have at hand.

Simplified version of problem:  We have a table.  We'll say it has four
variables, a, b, c, and squelch, all ints.  I want to get all values of
these variables for which:

c is within a certain range of values (call it between 3 and 5).
for any given value of a, c is maximum.
 - and -
for any given value of a for which c is maximal, b is maximum.  That is,
if we have the values of

a b c squelch
1 1 3 5
1 3 3 8
1 1 4 4
1 2 4 6
2 3 5 7

My query should, in this case, get only the last two rows.  (The last one
because there is only one row in which a=2, the next to last because c is
maximal for all values where a=1, and b is maximal for all values where c
= the maximal value for a=1.)

Does this make sense?  The actual question is more complicated than this,
but if I can figure out this, I can get the actual answer.  Please, if you
can, give me an actual sql command that I could use to get the proper
answers from this table.

Thanks a lot for your assistance.

Alloni Kramer

Re: SQL query question

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Alloni Kramer"  writes:
> Simplified version of problem:  We have a table.  We'll say it has four
> variables, a, b, c, and squelch, all ints.  I want to get all values of
> these variables for which:

> c is within a certain range of values (call it between 3 and 5).
> for any given value of a, c is maximum.
>  - and -
> for any given value of a for which c is maximal, b is maximum.  That is,
> if we have the values of

This isn't perfectly clear, but I think what you are after can be done
with

	select distinct on (a) * from mytab
	where c between 3 and 5
	order by a, c desc, b desc;

The "weather report" example in the SELECT reference page may be
helpful.

It's possible to do this without the nonstandard DISTINCT ON clause,
but it's a lot more tedious (and I forget just how at the moment ;-))

			regards, tom lane
FAQ