D
D
Darsatan2014-10-05 21:15:21
SQL
Darsatan, 2014-10-05 21:15:21

How to search by subgroup?

Good evening.
There is a certain table that stores computer configurations (id, inv_number, date, proc, memory, ...). When changing the configuration, a new line is added, which is guaranteed to match the entry about the previous version of the config only by the inv_number field (the rest may change). It is necessary to compile a list of current equipment (ie records with the latest dates)
Knowledge of sql is not very much, but there are 2 thoughts. The first is to use a subquery

select * 
from pc pc1
where pc1.date = (select max(date) from pc pc2 where pc1.inv_number = pc2.inv_number)

In general, it works, but it confuses the execution of a subquery for each record, which is not good for performance. The second thought was related to grouping by inv_number, but I have no idea how to then display rows with max(date).
In general, tell me how best to do it or push me on the right path.
UPD: Indeed, I screwed up with the condition in the subquery... SQL Fiddle

Answer the question

In order to leave comments, you need to log in

2 answer(s)
E
evnuh, 2014-10-06
@Darsatan

Not always a working option:
Always working option:

SELECT g.* from pc g 
INNER JOIN (
SELECT inv_number, MAX(date) as date FROM pc 
GROUP BY inv_number) 
AS s USING (inv_number, date)

A
asdz, 2014-10-05
@asdz

It would be desirable to throw an example here
Maybe like this:?

SELECT * FROM (
          SELECT * FROM pc ORDER BY date ASC
) tmp
GROUP BY inv_number
ORDER BY inv_number

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question