I have a table with data and I’m trying to select some values with lower value than maximum. I am using IBExpert, I need to do this in SQL because I am working on a Firebird database. My table looks like this:
UID TYP ID_RODZICA ID_DZIALKI PRZED_PO GRUPA DTW OSOW DTU OSOU 17 170 K 5 765 3 551 0 1 163 759 N 5 765 53 084 0 1 29.03.2018 11:45 21 163 760 N 5 765 49 796 1 1 29.03.2018 11:45 21 163 761 N 5 765 49 426 1 1 29.03.2018 11:45 21 163 762 N 5 765 53 085 1 1 29.03.2018 11:45 21 163 763 N 5 765 53 086 1 1 29.03.2018 11:45 21 163 764 N 5 765 53 087 0 2 29.03.2018 11:45 21 163 765 N 5 765 53 088 0 2 29.03.2018 11:45 21 163 766 N 5 765 8 940 0 2 29.03.2018 11:45 21 163 767 N 5 765 41 931 0 2 29.03.2018 11:45 21 253 171 N 5 765 41 931 0 3 29.03.2018 21:14 21 253 172 N 5 765 53 088 0 3 29.03.2018 21:14 21 253 173 N 5 765 8 940 0 3 29.03.2018 21:14 21 253 174 N 5 765 49 796 0 3 29.03.2018 21:14 21 253 175 N 5 765 49 426 0 3 29.03.2018 21:14 21 253 176 N 5 765 53 085 0 3 29.03.2018 21:14 21 253 177 N 5 765 53 086 0 3 29.03.2018 21:14 21 253 178 N 5 765 67 386 0 3 29.03.2018 21:14 21 253 179 N 5 765 67 387 0 3 29.03.2018 21:14 21 253 180 N 5 765 67 388 0 3 29.03.2018 21:14 21
What I want is to select only rows with GRUPA values lower than maximum, here max value is 3, but sometimes it might be 5, 7, 11 or whatever, so I can’t specify the value.
It should look like this:
UID TYP ID_RODZICA ID_DZIALKI PRZED_PO GRUPA DTW OSOW DTU OSOU 17 170 K 5 765 3 551 0 1 163 759 N 5 765 53 084 0 1 29.03.2018 11:45 21 163 760 N 5 765 49 796 1 1 29.03.2018 11:45 21 163 761 N 5 765 49 426 1 1 29.03.2018 11:45 21 163 762 N 5 765 53 085 1 1 29.03.2018 11:45 21 163 763 N 5 765 53 086 1 1 29.03.2018 11:45 21 163 764 N 5 765 53 087 0 2 29.03.2018 11:45 21 163 765 N 5 765 53 088 0 2 29.03.2018 11:45 21 163 766 N 5 765 8 940 0 2 29.03.2018 11:45 21 163 767 N 5 765 41 931 0 2 29.03.2018 11:45 21
I’ve tried with:
SELECT c1.*
FROM (select * FROM obdg where ID_RODZICA = '5765') c1
WHERE NOT EXISTS
(SELECT c2.*
FROM (select * from obdg WHERE ID_RODZICA = '5765') c2
WHERE c1.GRUPA = MAXVALUE(c2.GRUPA))
eliminating the max value, but result of that is simply nothing. When I use < instead of = before MAXVALUE(c2.GRUPA) I get:
UID TYP ID_RODZICA ID_DZIALKI PRZED_PO GRUPA DTW OSOW DTU OSOU 253 171 N 5 765 41 931 0 3 29.03.2018 21:14 21 253 172 N 5 765 53 088 0 3 29.03.2018 21:14 21 253 173 N 5 765 8 940 0 3 29.03.2018 21:14 21 253 174 N 5 765 49 796 0 3 29.03.2018 21:14 21 253 175 N 5 765 49 426 0 3 29.03.2018 21:14 21 253 176 N 5 765 53 085 0 3 29.03.2018 21:14 21 253 177 N 5 765 53 086 0 3 29.03.2018 21:14 21 253 178 N 5 765 67 386 0 3 29.03.2018 21:14 21 253 179 N 5 765 67 387 0 3 29.03.2018 21:14 21 253 180 N 5 765 67 388 0 3 29.03.2018 21:14 21
obviously, as I am eliminating all values lower than max (as far as I understand that).
I also tried something much simpler:
SELECT * FROM OBDG WHERE ID_RODZICA = '5765' AND GRUPA < MAXVALUE(GRUPA)
but again I get nothing in results window. If I change “MAXVALUE(GRUPA)” to just “3” I get expected results, but again, I can’t specify the max value.
What am I doing wrong?
Advertisement
Answer
As far as I can tell from your question, you want:
select a.*
from OBDG a
where a.ID_RODZICA = '5765'
and a.GRUPA <> (select max(GRUPA)
from OBDG b
where b.ID_RODZICA = a.ID_RODZICA)
That is: select all rows where GRUPA is not equal to the maximum value of GRUPA (for that ID_RODZICA).
As to why your queries don’t work:
In your first query, the subquery in the NOT EXISTS will always produce rows given the example data, and in a NOT EXISTS this means the result is always false, and therefor the query as whole produces no rows.
The second query you tried doesn’t work, because MAXVALUE does not do what you think it does. It is not an aggregate function, but instead from the list of values passed as parameters, it will return the highest value. Given you pass a single value, it will return that value. Which results in GRUPA < MAXVALUE(GRUPA) being equivalent to GRUPA < GRUPA which is never true, so again it produces no rows.