0

I read many threads but didn't get the right solution to my problem. It's comparable to this Thread

I have a query, which gathers data and writes it per shell script into a csv file:

SELECT
    '"Dose History ID"' = d.dhs_id,
    '"TxFieldPoint ID"' = tp.tfp_id,
    '"TxFieldPointHistory ID"' =  tph.tph_id,
    ...

FROM txfield t
LEFT JOIN txfielpoint tp ON t.fld_id = tp.fld_id
LEFT JOIN txfieldpoint_hst tph ON fh.fhs_id = tph.fhs_id
...

WHERE d.dhs_id NOT IN ('1000', '10000')
    AND ...
ORDER BY d.datetime,...;

This is based on an very big database with lots of tables and machine values. I picked my columns of interest and linked them by their built-in table IDs. Now I have to reduce my result where I get many rows with same values and just the IDs are changed. I just need one(first) row of "tph.tph_id" with the mechanics like 

WHERE "Rownumber" is 1

or something like this. So far i couldn't implement a proper subquery or use the ROW_NUMBER() SQL function. Your help would be very appreciated. The Result looks like this and, based on the last ID, I just need one row for every og this numbers (all IDs are not strictly consecutive).

A01";261511;2843119;714255;3634457;
A01";261511;2843113;714256;3634457;
A01";261511;2843113;714257;3634457;
A02";261512;2843120;714258;3634464;
A02";261512;2843114;714259;3634464;
....
Community
  • 1
  • 1
Nurito
  • 1
  • try `GROUP BY` [all the fields except tph.tph_id] while intended for aggregation, `GROUP BY` should give you the result you want – Jameson the dog Dec 14 '16 at 14:29
  • Perhaps I'm missing something, but right at the beginning of your select you have a fatal error. You cannot assign a value to a string (i.e. `'"Dose History ID"' = d.dhs_id` is illegal). Second, you are using an alias `d` (like in `WHERE d.dhs_id NOT IN ('1000', '10000')`) and I don't see where this alias is being defined. – FDavidov Dec 14 '16 at 14:30
  • @FDavidov the alias for d (and others) are followed later in the SELECT part. I tried – Nurito Dec 15 '16 at 10:53
  • Thx for your responses - @FDavidov the alias for d (and others) are followed later in the SELECT part. I tried to show with the "..." that this is just a part of the actual code, because the query is pretty big, but works in the way shown. This statement gives me in my csv the description "Dose History ID" in the first row and writes the values after the second row in the collumn. I tried it with GROUP BY but it isn't the case that every data in several rows are the same. Just the ID differs but it's enough to not grouping it. Or am I on the wrong track with this? – Nurito Dec 15 '16 at 11:04

1 Answers1

0

I think "GROUP BY" may suit your needs.

You can group rows with the same values for a set of columns into a single row

Jacopo
  • 525
  • 3
  • 15