SQL - Getting more data with a GROUP BY

Question

Hoping someone can help me out with this.

Lets say I have a table called incoming_data with 4 columns:

primary_key_id
serial_number
counter
selected_color

I get data from a source that fills this table. The primary key has its identity turned on so it is not repeating. I will get duplicate serial numbers with different selected colors.

The counter is ever increasing coming from the device.

So what I want to do is select all the data I received today, group the data by serial number and only get the record with the highest counter value.

Simple:

SELECT      serial_number, MAX(counter)
FROM        incoming_data 
GROUP BY    serial number

This works perfectly, except that I want to do something with the color information that I received.

If I add color to the select, then I get ALL the records since all the colors I received that day are different. That wont work.

If I could get the primary_key_id of the record then I could just query for the color but this doesn't work either since each primary_key_id value is different I get them all.

Any suggestions on a better technique for doing this?

Thanks!

Answer 1

EDIT: t odeal with cases where multiple records have same serial number and max counter value.. This will extract the one of those multiples with the biggest primary_key_id

   Select * From incoming_data 
    Where primary_key_id In 
       (Select Max(primary_key_id) From incoming_data I
        Where Counter = 
              (Select Max(counter) From incoming_data 
               Where SerialNumber = I.SerialNumber)
        Group By SerialNumber)

Answer 2

Extract the relevant key, then join back to get "non key". This works in MS SQL Server and later Sybase.

SELECT 
    i.serial_number, i.counter, i.selected_color
FROM
   (
   SELECT      serial_number, MAX(counter) AS maxc
   FROM            incoming_data 
   GROUP BY    serial number
   ) max
   JOIN
   incoming_data i ON max.serial_number = i.serial_number AND max.maxc = i.counter

Answer 3

Hi I think this might do what you want?

SELECT selected_color, serial_number, counter FROM incoming_data GROUP BY serial_number ORDER BY counter DESC LIMIT 1;

Answer 4

If you don't need the exact color from the query, but could parse it afterwards, try this:

SELECT      serial_number, MAX(counter), MAX( CONCAT(counter, ':', selected_color) )
FROM            incoming_data 
GROUP BY    serial number

Answer 5

Hello, I am trying to get the max numbers from two joined tables. I tried to use DISTINCT to avoid repeatative numbers, but failed. Then I found the following query and works perfect on single table, but I failed to have it worked on joined two tables.

How to implement the following query on my query: select top 10 ConsumptionKWH from PeriodicConsumptions group by ConsumptionKWH order by min(ConsumptionKWH) asc

Needed on:

" SELECT * FROM " & _ " (SELECT Distinct top 10 " & _ " PeriodicConsumptions.ConsumptionKWH, " & _ " MeterReadings.MeterNo, " & _ " MeterReadings.LastKWH," & _ " MeterReadings.AccountNo, " & _ " MeterReadings.MultiplyFactor," & _ " MeterReadings.LastReading, " & _ " MeterReadings.ThisMonthReading," & _ " MeterReadings.NumberofDays," & _ " MeterReadings.ReadingGYear, " & _ " MeterReadings.ReadingGMonth, " & _ " MeterReadings.ReadingDate " & _ " FROM MeterReadings, PeriodicConsumptions" & _ " Where MeterReadings.AccountNo = PeriodicConsumptions.AccountNo " & _ " and MeterReadings.ReadingGYear =PeriodicConsumptions.ConsumptionYear " & _ " and MeterReadings.ReadingGMonth =PeriodicConsumptions.ConsumptionMonth " & _ " and MeterReadings.ReadingDate = PeriodicConsumptions.FromDate " & _ " ORDER BY PeriodicConsumptions.ConsumptionKWH desc) as t " & _ " ORDER BY ConsumptionKWH asc"

Thanks