SQL Server query should return max value records

Question

I have table like this:

id_Seq_No   emp_name     Current_Property_value  
-----------------------------------------------
1           John              100
2           Peter             200
3           Pollard            50
4           John              500

I want the max record value of particular employee.

For example, John has 2 records seq_no 1, 4. I want 4th seq_no Current_Property_Value in single query.

Select  
    max(id_Seq_No) 
from 
    t1 
where 
    emp_name = 'John'

If you want the maximum property value why are you using max on ID ? Why not max(Current_Property_Value) ? Did you mean latest instead of maximum perhaps? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 18, 2016 at 9:28
@PanagiotisKanavos he wants the Current_Property_value for the "maximum" id_Seq_No matching a certain emp_name — BytesOfMetal
– BytesOfMetal, Commented Aug 18, 2016 at 9:32
@msanz that's not what the question or title says, although one could infer this. The title and text should be rewritten — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 18, 2016 at 9:34
@PanagiotisKanavos totally agree with you, just tried to throw some light on it — BytesOfMetal
– BytesOfMetal, Commented Aug 18, 2016 at 9:36

fedorqui · Accepted Answer · 2016-08-18 09:26:25Z

2

To get the Current_Property_value, just order the results by id_Seq_No and get the first one:

SELECT
   TOP 1 Current_Property_value
FROM
   table
WHERE
   emp_name = 'John'
ORDER BY
   id_Seq_No DESC

answered Aug 18, 2016 at 9:26

fedorqui

294k113 gold badges592 silver badges640 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2016-08-18 09:30:26Z

0

this will give highest for all tied employees

select top 1 with ties
id_Seq_No,emp_name,Current_Property_value 
from
table 
order by
row_number() over (partition by emp_name order by Current_Property_value desc)

answered Aug 18, 2016 at 9:30

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

3 Comments

Panagiotis Kanavos Over a year ago

You don't need TOP/ORDER BY when you use ROW_NUMBER, just check its value against 1 in the WHERE clause. You are forcing an unnecessary sort.

TheGameiswar Over a year ago

@PanagiotisKanavos: just check its value against 1 ..? you mean using cte and check rownum=1

TheGameiswar Over a year ago

@PanagiotisKanavos:Top is used with ties to restrict only top 1 elements after ordering by rownumber

Ullas · Accepted Answer · 2016-08-18 10:11:22Z

0

You can use ROW_NUMBER with CTE.

Query

;WITH CTE AS(
    SELECT rn = ROW_NUMBER() OVER(
        PARTITION BY emp_name
        ORDER BY id_Seq_No DESC
    ), * 
    FROM your_table_name
    WHERE emp_name = 'John'
)
SELECT * FROM CTE
WHERE rn = 1;

answered Aug 18, 2016 at 10:11

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Collectives™ on Stack Overflow

SQL Server query should return max value records

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related