CODEWITHSUNDEEP
sql-server
masiboo
masiboo

Reputation: 4719

SQL Server query select a row of the table where 2 conditions must be true

I have a table with 6 columns 

No, Id, Created, Snapshot, Note, Size

Please see the screenshot to understand the table structure.

enter image description here

I want to select the row where Note Like "default" and the No has the maximum value. Please note that Note could contain several same values like "default"

It might be a very basic question. But asked already. But I couldn't find any that works for me. So far I tried:-

SELECT * 
FROM Mytable
WHERE Note = 'defalut' AND No = (SELECT MAX(No) FROM Mytable)

I got no result. Please let me know how to get it. Thanks in advance!

Upvotes: 0

Views: 39

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way using Max Over() window function 

SELECT *
FROM   (SELECT *,
               MaxNo = Max(No)OVER()
        FROM   Mytable
        WHERE  Note = 'default') a
WHERE  No = MaxNo

Upvotes: 1

John Woo
John Woo

Reputation: 263683

You can use ROW_NUMBER() in this case to get the largest No

SELECT  No, Id, Created, Snapshot, Note, Size
FROM 
    (
        SELECT  *,
                RN = ROW_NUMBER () OVER (ORDER BY No DESC)
        FROM    MyTable
        WHERE   Note = 'default'
    ) a
WHERE RN = 1

However, if No is unique for all rows, you can just remove add Note filter in subquery

SELECT No, Id, Created, Snapshot, Note, Size
FROM   Mytable
WHERE  No = (SELECT MAX(No) FROM Mytable WHERE Note = 'default')

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

You are almost correct:

SELECT * 
FROM Mytable
WHERE Note = 'default' AND No = (SELECT MAX(No) FROM Mytable WHERE Note = 'default')

Upvotes: 0

Related Questions