0

I am able to get data from DB that I need, except that I only want one row for each date, so for example in the picture below there should be only the second row not the first. I somehow need to be able to get just one row for one user from one date.

SELECT id_user, date_created || ' ' || time_created as placed
            FROM (
                        SELECT
                        b_objednavky.uzivatel AS id_uzivatele,
                        b_objednavky.vytvoreno::date AS date_created,
                        to_char(b_objednavky.vytvoreno, 'HH24:MI:SS') as time_created,
                        b_objednavky.created

                    FROM b_objednavky
                    INNER JOIN u_uzivatele ON b_objednavky.uzivatel = (
                     SELECT u_uzivatele.id from u_uzivatele JOIN bw_paid_orders
                           ON u_uzivatele.id = bw_paid_orders.user_id 
                     WHERE bw_paid_orders.active_thru < NOW() + interval '6 months'
                     ) uzivatele
                    INNER JOIN bc_stavy_objednavky ON b_objednavky.stav = bc_stavy_objednavky.id

                    WHERE bc_stavy_objednavky.sysid = 'nova' AND b_objednavky.vytvoreno <= NOW()
                    AND bc_staty.kod NOT IN (
                      'BR', 'CL', 'AR', 'MX'
                    )

                ) AS order
                 LEFT OUTER JOIN (
                  SELECT user_id, max(paid) as last_paid FROM bw_paid_orders
                GROUP BY user_id
                ) last_paid_by_user ON (id_user = user_id) 
                WHERE (created > last_paid OR last_paid IS NULL) AND created BETWEEN ('2017-02' || '-01')::date
            AND ('2017-02' || '-01')::DATE + '1 month'::interval 
            ORDER BY datum_objednani DESC, objednavajici, nazev, duration
rtom
  • 585
  • 1
  • 12
  • 26
  • Put into a CTE by using Row_Number Over(Partition By whatever reference u are using ) and select from that CTE where rownumber = 1 – Anwar Ul-haq Feb 22 '17 at 15:56

1 Answers1

2

Use DISTINCT ON

to get just one row for one user from one date

SELECT DISTINCT ON (id_user, date_created) ...

To get the row with the latest time for the day, add (at the same query level):

ORDER BY id_user, date_created, time_created DESC NULLS LAST

Details:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • And where should I use it ? in which clause ? I am kind of lost on that – rtom Feb 22 '17 at 15:57
  • 1
    @jemcaj: in the `select` right at the start, just like Erwin showed –  Feb 22 '17 at 15:58
  • @jemcaj: Did you follow the link? – Erwin Brandstetter Feb 22 '17 at 15:59
  • It throws error pointing at `as` before placed, If I put It only at `date_created` then it would not consider time because in that variable there is only date or no ? .. @ErwinBrandstetter I looked at It, but I don't think that I understand what it does – rtom Feb 22 '17 at 16:02
  • 1
    @jemcaj: Why consider the time to `get just one row for one user from one date`? The only question remaining: *which* row do you want to get per `(id_user, date_created)` if there are more than one. The answer ... is in the linked answer. – Erwin Brandstetter Feb 22 '17 at 16:05
  • Okay so `DISTINCT ON` will only return unique resuts that I understand. But If I try to use it I get error `SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT COUNT(*) AS total FROM (SELECT DISTINCT ON(id_user...` – rtom Feb 22 '17 at 16:12
  • 1
    @jemcaj: I added a working `ORDER BY` above. The explanation is ... in the linked answer. If you need a different order in the result, encapsulate in a subquery and order differently in the outer query. – Erwin Brandstetter Feb 22 '17 at 16:14
  • @ErwinBrandstetter thank you ! After 15 hours it's finally working ! – rtom Feb 22 '17 at 16:18
  • 1
    @jemcaj: Ah, the relief! We've all been there. I can feel it :) – Erwin Brandstetter Feb 22 '17 at 16:19