Learn, Share, Build

261
September 20, 2017, at 1:34 PM

My Data look like :

I want to fetch first record of every dtcode with mini time of occurrences.

Desired Output :

By using scala i want to fetch. Please guide me in building the logic.

Thanks, Syam.

Answer 1

--- EDITED with correct comment from Shaido ---

Usually, GROUP BY would handle this, if the order in the table is irrelevent,

SQL is good with grouping large amount of related data. However, your analysis is dependent on the order of data entry and the change is triggered by a change in one column which can be repeated later and can't be aggregated, while the other columns can continue changing.

In this case, you will need to LOOP over your data and detect the changes manually, as SQL doesn't have an easy way to group this kind of things. I answered a little too fast and had not noticed this.

This is best handled through a STORED PROCEDURE or a display language. I can give you the code in PHP if you'd like.

Another cheat will be to add a column that is used by GROUP BY (let's call it groubycheat) that is increased every time dtcode changes

SELECT MIN(Currentdatedtime) as Currentdatedtime, dtcode
FROM <tablename>
GROUP BY groupbycheat;

This STILL requires a LOOP to add the field, but if you need to get the results many times, then it's worth it. Otherwise ..no

Rent Charter Buses Company
READ ALSO
Learn, Share, Build

Learn, Share, Build

I made a platform to remind me of some things I haven't doneI've stored this reminds into a database and now I want this PHP based Website to send me emails

214
Learn, Share, Build

Learn, Share, Build

1) Change

200
Learn, Share, Build

Learn, Share, Build

Table name - Masterdata

234