Selecting the right Row - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Selecting the right Row

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 06:37:07

I have inherited a table which stores reference numbers

[id] bigint
[FileNo] varchar(3)
[ more fields ] etc

The table holds over a thousand records. the id field is unique however the fileNo field is not. I am given the fileno value and need to retrieve the record with the largest ID

[12345] [159]
[100] [159]
[179854] [159]

I need to be able to return the record with id = 179854 when select with FileNo = 159

I have tried Top[1], and max but with no joy .. help !

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 06:44:09

max doesnt work?

select max(id)
from table
where fileno = 159

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 08:16:31

Either:


SELECT TOP 1
    [ID]
    , ......
    , ......
FROM
     <table>
WHERE
    [fileNo] = '159'
ORDER BY
    [ID] DESC

OR the derived table version


SELECT
    t.<whatever>
FROM
    <Table> AS t

    JOIN (
        SELECT
            MAX([ID]) AS [maxID]
            , [fileNo] AS [fileNo]
        FROM
            <table>
        GROUP BY
            [FileNo]
        )
        AS highestId ON highestId.[maxId] = t.[ID]
WHERE
    [fileNo] = '159'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources