CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-database
Night Walker
Night Walker

Reputation: 21280

Group By and Selection of columns

I have the following query 

SELECT  McId, MAX(TimeDone) 
FROM    Trace 
WHERE   PcbId = 'C825514362' 
AND  DeviceId <> 0 GROUP BY McId

But I also need to get values from following columns Program,DeviceId,OrderNo

Is it possible to somehow combine that in the same query ?

This query should run under mssql , mysql and oracle.

Upvotes: 0

Views: 101

Answers (5)

JHS
JHS

Reputation: 7871

Three solutions.

  1. Considering you want the MAX of Program,DeviceId,OrderNo

    SELECT McId, 
       MAX(TimeDone),
       MAX(Program),
       MAX(DeviceId),
       MAX(OrderNo)
FROM  Trace
WHERE   PcbId = 'C825514362' AND  DeviceId  0
GROUP BY McId;

  2. Considering you want the remaining columns as comma separated with ~ as the separator 

    SELECT McId, 
       MAX(TimeDone),
       GROUP_CONCAT('~', Program, '~', DeviceId, '~', OrderNo)
FROM  Trace
WHERE   PcbId = 'C825514362' AND  DeviceId  0
GROUP BY McId

  3. Considering you want to be grouped along with McId

    SELECT McId, 
       MAX(TimeDone),
       Program,
       DeviceId,
       OrderNo
FROM  Trace
WHERE   PcbId = 'C825514362' AND  DeviceId  0
GROUP BY McId, Program, DeviceId, OrderNo;

Upvotes: 0

nina
nina

Reputation: 76

Well, I'd guess that you want to get the values of Program, DeviceId and OrderNo for the row which contains the maximum of TimeDone. As far as I know, you have to do that with a subquery. Something like that: 

SELECT t.McID, t.Program, t.DeviceID, t.OrderNo, t.TimeDone
FROM Trace t,
   (SELECT MAX(TimeDone) as maxtime, McID
 FROM Trace 
 GROUP BY McID) maxresults
WHERE t.McID = maxresults.McID
AND t.TimeDone= maxresults.maxtime;

Upvotes: 2

Diego
Diego

Reputation: 36166

why cant you add Program,DeviceId,OrderNo to the group by?

If you answer that it will produce more lines because there are different values for "Program,DeviceId,OrderNo" than which of the values would you like to display?

you can always embed a subquery on you group by to get this values but again, if you have more than one possibility, you need to define the rule to come up with the program for example

SELECT  McId, MAX(TimeDone), 
        (select top 1 program from trace t2 where t2.McId = trace.McId) as program
FROM  Trace
WHERE   PcbId = 'C825514362' AND  DeviceId <> 0
GROUP BY McId

Upvotes: 0

Ankit
Ankit

Reputation: 690

have you tried the following construct?

SELECT McId, MAX(TimeDone),Program,DeviceId,OrderNo FROM Trace WHERE PcbId = 'C825514362' AND  DeviceId <> 0 GROUP BY McId,Program,DeviceId,OrderNo

Upvotes: 0

iiro
iiro

Reputation: 3118

SELECT  
 McId, 
 MAX(TimeDone),
 Program,
 DeviceId,
 OrderNo
FROM  Trace
WHERE   PcbId = 'C825514362' AND  DeviceId <> 0
GROUP BY 
 McId,
 Program,
 DeviceId,
 OrderNo

Upvotes: 1

Related Questions