CODEWITHSUNDEEP
sqlsql-server
Alex
Alex

Reputation: 5724

How to get details of row with an aggregate function without subquery?

How can I get the record details of an aggregate function without using a sub query?

I have a data set as follows: 

CREATE TABLE SOMEDATA(
id int, 
low int, 
op int,
yc int,
tc int,
volume int,
Atimestamp time)

INSERT INTO SOMEDATA VALUES(5631,       5500,       5600,   5680,           5680,       300527,     '13:16:12.462')
INSERT INTO SOMEDATA VALUES(5631,       5500,       5600,   5680,           5680,       301720,     '13:16:13.304')
INSERT INTO SOMEDATA VALUES(5631,       5500,       5600,   5680,           5680,       302041,     '13:16:13.306')
INSERT INTO SOMEDATA VALUES(5631,       5500,       5600,   5680,           5680,       302410,     '13:16:13.682')
INSERT INTO SOMEDATA VALUES(5631,       5500,       5600,   5680,           5680,       302548,     '13:16:15.818')
INSERT INTO SOMEDATA VALUES(5632,       5500,       5600,   5680,           5680,       302548,     '13:16:15.818')

Which I query by doing:

SELECT * FROM SOMEDATA 
INNER JOIN (select max(Atimestamp) as tm,id FROM SOMEDATA group by id) t 
on t.tm = SOMEDATA.Atimestamp AND SOMEDATA.id = t.id

This seems like a bad way to do it though ( eg as I understand it, this query locks the table twice) - is there a better way to do this ( with HAVING perhaps )?

Upvotes: 0

Views: 256

Answers (3)

KeepCalmAndCarryOn
KeepCalmAndCarryOn

Reputation: 9075

  SELECT id, low, op, yc, tc, max (Atimestamp)
  FROM SOMEDATA 
  GROUP BY id, low, op, yc, tc

For an aggregate column you need to use a GROUP BY

Upvotes: 0

TechDo
TechDo

Reputation: 18649

Please try:

SELECT * FROM(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY Atimestamp DESC) RNum
    From SOMEDATA
)x
WHERE RNum=1

OR

;WITH x AS(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY Atimestamp DESC) RNum
    From SOMEDATA
)
SELECT * FROM x
WHERE RNum=1

Upvotes: 1

StevieG
StevieG

Reputation: 8709

You should be able to use the RANK() function for this. Something like:

SELECT id, low, op, yc, tc, volume, Atimestamp
FROM
(
  SELECT 
    id, 
    low, 
    op,
    yc,
    tc,
    volume, 
    Atimestamp,
    RANK() OVER (PARTITION BY id ORDER BY Atimestamp DESC) AS rank
FROM somedata
) a
WHERE a.rnk = 1

Upvotes: 1

Related Questions