CODEWITHSUNDEEP
sql
Daniel
Daniel

Reputation: 11054

SQL -- Selecting Top 1 with Order by?

This was resolved. The statement was in another part of the stored procedure.

The stored procedure I'm writing won't allow me to do this:

 declare @dtTopDate datetime
  select top 1 @dtTopDate  = date_build
    from database..table
   where database..table.parent = @Parent
     and database..table.child = @Child
   order by date_build desc

Gives me this error:

Column "database..table.date_build" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

What am I doing wrong?

[Edit] There is no group by statement here. SQL2005.

Here is some more context:

if @Notify = 0
begin
     declare @dtTopDate datetime
      select top 1 @dtTopDate  = date_build
        from database..table
       where database..table.parent = @Parent
         and database..table.child = @Child
       order by date_build desc

      insert 
        into database2..table
             (parent, child, notification_date, change_date)
      values (@Parent, @Child, @dtTopDate, getdate())
     return 
end

Upvotes: 0

Views: 7475

Answers (8)

Will Charczuk
Will Charczuk

Reputation: 919

if you want to get really tricky, in T-SQL you can try using the row_number() method and an inner select:

select * from
(
    select 
        db.groupId
        , db.date_build
        , date_build_rank = row_number() over ( partition by db.groupId order by db.date_build desc)
    from
        #date_build_tbl db
) as a 
where a.date_build_rank < 2;

Upvotes: 0

user109386
user109386

Reputation: 21

Instead of SELECT TOP 1 ... ORDER BY ...

Why not try SELECT MAX( ..

DECLARE @dtTopDate datetime
SELECT @dtTopDate = MAX(date_build)
 from database..table 
 where database..table.parent = @Parent
 and database..table.child = @Child

Upvotes: 2

Daniel
Daniel

Reputation: 11054

The problem was in another part of the stored procedure. I was using a count(*) elsewhere and it required a group by. Thanks for the help.

Upvotes: 1

Zack
Zack

Reputation: 2341

This works for me, but I'm not sure if this is what you are trying to do b/c your example has some errors.

use Test
go
CREATE TABLE [dbo].[MyTable]
(
    [MyTableId] [uniqueidentifier] NOT NULL,
    [MyDate] [datetime] NOT NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED([MyTableId] ASC,[MyDate] ASC)
)
GO
CREATE PROCEDURE ProcTopDate
(
    @MyDate datetime OUT
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP 1
        @MyDate = [MyDate]
    FROM [Test].[dbo].[MyTable]
    order by MyDate
END
GO

insert into MyTable(MyTableId, MyDate)
values(newid(), getdate())
go

declare @MyDate datetime
exec ProcTopDate @MyDate OUT
print @MyDate

Upvotes: 2

gbn
gbn

Reputation: 432210

Try qualifying the columns correctly to avoid any ambiguities or x-database schema issue

declare @dtTopDate datetime

select top 1 
    @dtTopDate  = [database]..[table].date_build
FROM
    [database]..[table]
where
    [database]..[table].parent = @Parent
    and [database]..[table].child = @Child
order by
    [database]..[table].date_build desc

Or alias it

declare @dtTopDate datetime

select top 1
    @dtTopDate  = foo.date_build
FROM
    [database]..[table] foo
where
    foo.parent = @Parent
    and foo.child = @Child
order by
    foo.date_build desc

Upvotes: 1

Tawani
Tawani

Reputation: 11198

Honestly the only thing I can see wrong is that @dtTopDate =/= @dtLatestDate Apart from that, there is no GROUP BY clause in your SQL statement.

I just ran this and it worked fine.

declare @OrderDate datetime

select top 1 @OrderDate = OrderDate
from Orders
where Orders.CustomerID = 'ALFKI'
 and Orders.EmployeeID = 4
order by OrderDate desc

SELECT @OrderDate

Upvotes: 1

Rich.Carpenter
Rich.Carpenter

Reputation: 1056

Try SELECT @dtLatestDate = TOP 1 date_build...

Upvotes: 0

Ian Horwill
Ian Horwill

Reputation: 3025

What version of SQL are you using? It works fine for me on MS SQL Server 2005 (ionce I fix the declaration).

Upvotes: 1

Related Questions