How to select a row with GROUP BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to select a row with GROUP BY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-07 : 06:56:32

Magnus writes "I have a probably very easy problems for anyone who knows msSQL better than me.

I have a Query like this:

SELECT InvId, LopNr, MAX(kal_datum) AS LastKal, kal_godkand
FROM dbo.Inventarie_Kalibrering
GROUP BY InvId, LopNr

Its basicly a table with 2 ID's InvId and LopNr one date and one true/false field.
Its used to collect all the calibrations for our instruments so for every calibration there will be a row telling using InvId and LopNr to identify the item and then a date and if its working (true/false).

What i want from that data now is a list of all the latest calibrations and if its working, my problem is that the query above isnt working due to the "kal_godkand" isnt in the group by. but i cant put it in group by because then i will get 2 "lastest" tests one when it was latest tested and flagged as working and one when it was last tested and failed.

Anyone has a idea how to get this to work? if im not wrong i think i remember using querys like this in other database brands without any problems. but for some reason msSQL seems more picky.

Whould be very happy for a sollution to my headache.

Best regards
/Magnus Olsson"

Kristen
Test

22859 Posts

Posted - 2005-09-07 : 07:09:14

So ... you want the most recent kal_godkand for each InvId, LopNr combination?


SELECT InvId, LopNr, kal_datum AS LastKal, kal_godkand
FROM dbo.Inventarie_Kalibrering AS IK
     JOIN 
     (
         SELECT  InvId, LopNr, MAX(kal_datum) as LastKal
         FROM    dbo.Inventarie_Kalibrering
         GROUP BY InvId, LopNr
     ) AS X
        ON X.InvId = IK.InvId
       AND X.LopNr = IK.LopNr
       AND X.LastKal = IK.kal_datum
ORDER BY InvId, LopNr

There will be duplicates if is is possible to have multiple InvId, LopNr with the exact same date

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources