CODEWITHSUNDEEP
sqlsql-server
JonathanWolfson
JonathanWolfson

Reputation: 861

How do you Select the Top record from a Group of Rows from a Table in a database?

I am SELECTing a group of records but one column yields distinct values in two or more results. I just want to SELECT the top value; not the Min one or the Max value, but the Top N value encountered by SQL Server 2008. Is there any aggregation mechanism that will perform this?

E.g.

Table has:

Microsoft MSFT 12/21/05
Microsoft MSFT 10/22/05
Microsoft MSFT 11/23/06
Paramount PMNT 02/21/05
Paramount PMNT 01/23/06

 I execute:

SELECT   [Name], [Symbol], PriceDate
FROM     aaa
GROUP BY [Name], [Symbol]

The desired result is:

Microsoft MSFT 12/21/05
Paramount PMNT 02/21/05

(TOP fails)

Upvotes: 0

Views: 510

Answers (5)

JonathanWolfson
JonathanWolfson

Reputation: 861

SELECT [Name], [Symbol], PriceDate
FROM   aaa
WHERE  PriceDate =
(
   SELECT Top 1 aaa_2.PriceDate FROM aaa aaa_2
    WHERE aaa_2.[Name]   = aaa.[Name]
      AND aaa_2.[Symbol] = aaa.[Symbol]
)

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81930

Hm, I think all the answers do NOT answer the question (but of course maybe I got the question wrong):

If you do not group, you might e.g. get MSFT twice so we start with something like this

select name, symbol, x(date)
from sometable 
group by name, symbol

The question as I get it is concerned with the function x() which is to return the first element of the date column in the respective group. The problem is: there is no natural order of rows in a relational database. So such a function can't exist, since it is not defined.

You need another column defining the order, e.g. the column timestamp:

select 
    a.name, 
    a.symbol, 
    (
        select b.date 
        from sometable 
        where b.timestamp = min(a.timestamp) 
        and a.name = b.name
        and a.symbol = b.symbol
    ) as first_date
from sometable as a
group by name, symbol

This at least works in oracle. If sqlserver doesn't like this one can rewrite it as a join. The alternative would be analytic functions which I was told are supported by sqlserver

Upvotes: 1

Fredou
Fredou

Reputation: 20120

SELECT TOP 1   [Name], [Symbol], PriceDate
FROM     aaa
GROUP BY [Name], [Symbol]

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135021

one way, you can also use a variable instead of 1 since sql server 2005

select top (1) * 
from SomeTable
order by SomeColumn

Upvotes: 0

JohnIdol
JohnIdol

Reputation: 50097

Select TOP doesn't make sense if you don't specify the order (with ORDER BY smt), so you wanna do smt like:

SELECT TOP N *
FROM myTable
ORDER BY anyFields

You'll possibly get inconsistent results (there's nothing assuring the opposite) without order by.

Upvotes: 0

Related Questions