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

Question

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 )?

possible duplicate of [Get top 1 row of each group](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — GSerg, Jul 08 '13 at 11:43
You can use window-function like `ROW_NUMBER` but I don't think you'll get much if any difference in performance. Basics of execution plan should be the same and you query is also just fine. — Nenad Zivkovic, Jul 08 '13 at 11:47
@GSerg Yea, it is a duplicate, but the linked answer doesn't contain an inline rank function, which is what I was looking for. — Alex, Jul 08 '13 at 11:56
@Alex Yes it does, it's called `row_number`. No different to `rank` in this case. — GSerg, Jul 08 '13 at 11:57
Difference between `RANK` and `ROW_NUMBER` would be if there are two rows with exactly the same `Timestamp` value (for same id). `RANK` would return both of them as 1, `ROW_NUMBER` only one of them. — Nenad Zivkovic, Jul 08 '13 at 12:00
@GSerg Ow my bad, I was under the impression that the Rank function has different execution plan than the WITH CTE AS.. ROW_Number() pattern. — Alex, Jul 08 '13 at 12:12

score 1 · Accepted Answer · answered Jul 08 '13 at 11:45

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

score 1 · Answer 2 · answered Jul 08 '13 at 11:45

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

score 0 · Answer 3 · answered Jul 08 '13 at 11:47

0

  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

answered Jul 08 '13 at 11:47

KeepCalmAndCarryOn

8,817
2
32
47

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

3 Answers3