What else do I need to add to my SQL query to bring related information in other columns if using MI-CodePudding

There is a table with the following column headers: indi_cod, ries_cod, date, time and level. Each ries_cod contains more than one indi_cod, and these indi_cod are random consecutive numbers.

Which SQL query would be appropriate to build if the aim is to find the smallest ID of each ries_cod, and at the same time bring its related information corresponding to date, time and level? I tried the following query:

SELECT MIN (indi_cod) AS min_indi_cod FROM my-project-01-354113.indi_cod.second_step GROUP BY ries_cod ORDER BY ries_cod

And, indeed, it presented me with the minimum value of indi_cod for each group of ries_cod, but I couldn't write the appropriate query to bring me the information from the date, time and level columns corresponding to each indi_cod.

CodePudding user response：

I usually use some kind of ranking for this type of thing. you can use row_number, rank, or dense_rank depending on your rdbms. here is an example.

  with t as(select a.*,
 row_number() over (partition by ries_cod, order by indi_cod) as rn
 from mytable)
    select * from t where rn = 1

in addition if you are using oracle you can do this without two queries by using keep.

https://renenyffenegger.ch/notes/development/databases/SQL/select/group-by/keep-dense_rank/index

CodePudding user response：

I think you just need to group by with the other columns

SELECT MIN (indi_cod), ries_cod, date, time, level AS min_indi_cod 
FROM mytavke p 
GROUP BY ries_cod, date, time, level 
ORDER BY ries_cod