RETURNING FIRST ROW OF MULTIPLE ITEMS..

[Yammyguy]

Hello all,

 

I'm stuck on a query.  Basically, I would like to return the FIRST row for each item selected that match the criteria specified.

 

For example, I have 73 names, and I would like to return the first row for each name from a table that match my criteria...

 

here's the code I've got so far...

 

SELECT TOP 1 d.DEVICE_NAME, lpd.INTERVAL_DATE
FROM LOAD_PROFILE_DETAILS AS lpd
INNER JOIN DEVICES AS d ON (lpd.DEVICE_ID = d.DEVICE_ID)
WHERE d.DEVICE_ID IN (
SELECT DEVICE_ID FROM DEVICES
WHERE DEVICE_NAME LIKE 'KWH%'
AND PROVISION_STATUS = 2
) 
AND  lpd.RAW_LOAD_VALUE > 0

 

I'm not getting any errors, but the query will run for ever.

Any help would be greatly appreciated!

 

Thanks very much in advance!

 

[kickstart]

Hi

 

Not sure which "first row" you mean.

 

However you current code could just be written as:-

 

SELECT TOP 1 d.DEVICE_NAME, lpd.INTERVAL_DATE

FROM LOAD_PROFILE_DETAILS AS lpd

INNER JOIN DEVICES AS d

ON (lpd.DEVICE_ID = d.DEVICE_ID)

WHERE DEVICE_NAME LIKE 'KWH%'

AND PROVISION_STATUS = 2

AND  lpd.RAW_LOAD_VALUE > 0

 

All the best

 

Keith

[Yammyguy]

Hi

 

Not sure which "first row" you mean.

 

However you current code could just be written as:-

 

SELECT TOP 1 d.DEVICE_NAME, lpd.INTERVAL_DATE

FROM LOAD_PROFILE_DETAILS AS lpd

INNER JOIN DEVICES AS d

ON (lpd.DEVICE_ID = d.DEVICE_ID)

WHERE DEVICE_NAME LIKE 'KWH%'

AND PROVISION_STATUS = 2

AND  lpd.RAW_LOAD_VALUE > 0

 

All the best

 

Keith

 

What I mean by first row is that every device id has a bunch of rows of data, but I just want the FIRST row (the earliest) row of recorded data...

[kickstart]

Hi

 

Think this is the kind of thing you want:-

 

SELECT *
FROM DEVICES d
INNER JOIN LOAD_PROFILE_DETAILS AS lpd
ON d.DEVICE_ID = lpd.DEVICE_ID
INNER JOIN (SELECT DEVICE_ID, MAX(INTERVAL_DATE) AS MaxIntervalDate FROM LOAD_PROFILE_DETAILS GROUP BY DEVICE_ID) lpd2
ON lpd.DEVICE_ID = lpd2.DEVICE_ID
AND lpd.INTERVAL_DATE = lpd2.MaxIntervalDate
WHERE DEVICE_NAME LIKE 'KWH%'
AND PROVISION_STATUS = 2
AND  lpd.RAW_LOAD_VALUE > 0

 

This is joining the devices table with the load profile table, and also with a subselect of the load profile table which is getting the latest INTERVAL_DATE for each device id.

 

Note that if the RAW_LOAD_VALUE of the latest matching record on the load profile table is less than or equal to zero then you will not get a record back for that row. If what you want is the latest load profile table record which has a RAW_LOAD_VALUE greater than 0 then move that check into the subselect for the max INTERVAL_DATE.

 

All the best

 

Keith