Get maximum value row from SQL table

Question

3.40/5 (2 votes)

See more:

C#

I am writing code for one of project. So for so good. Now I struck up to get particular row which the value is maximum from column OeRT. Below is my table, this is one shift data entered on 5th Aug. When I query with OeDate, OeShift & OeLoct, I am getting this table in DataSet Visualizer. But I want the row which is maximum value which recorded in OeRT column. In this case I want only row number 800053, which is recorded OeRT value 131

.

What I have tried:

I am writing code for one of project. So for so good. Now I struck up to get particular row which the value is maximum from column OeRT. Below is my table, this is one shift data entered on 5th Aug. When I query with OeDate, OeShift & OeLoct, I am getting this table in DataSet Visualizer. But I want the row which is maximum value which recorded in OeRT column. In this case I want only row number 800053, which is recorded OeRT value 131. Pl, help me to get only one which contains maximum value in OeRT. My stored procedure also entered below.

OeID OeDate OeShift OeLoct OePOT OeSPT OeUPT OeRT
800052 05-08-2016 12:00:00 Shift_A 4 480 425 212 53
800053 05-08-2016 12:00:00 Shift_A 4 480 425 212 131
800054 05-08-2016 12:00:00 Shift_A 4 480 425 212 123
800055 05-08-2016 12:00:00 Shift_A 4 480 425 212 108
800056 05-08-2016 12:00:00 Shift_A 4 480 425 212 123

Stored Procedure
USE [TPM]
GO
/****** Object: StoredProcedure [dbo].[sp_OeeEntryDisplay] Script Date: 8/25/2016 3:15:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_OeeEntryDisplay]
@sCond nvarchar(max)
as
begin
declare @statement varchar(max);
set @statement = 'select OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT,OeRT,
from TPM_OeeEntry

where 1=1' + @sCond

execute(@statement);
end

Posted 25-Aug-16 0:10am

ShivAkshay

Updated 25-Aug-16 0:39am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

The Praveen Singh · Answer 1 · 2016-08-25T00:34:00

You can use Temporary tables to achieve your goal.

SQL

ALTER proc [dbo].[sp_OeeEntryDisplay]
AS
Create Table #Temp
(
SN           int Identity,
OeID         varchar(50),
OeDate       Datetime,  
OeShift      varchar(50),
OeLoct       varchar(50),
OePOT        varchar(50),
OeSPT        varchar(50),
OeUPT        varchar(50),
OeRT         varchar(50),
)
Insert into #temp(OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT ,OeRT                                 )
select OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT,OeRT,
from TPM_OeeEntry order by  OeRT desc

select TOP 1 * from #temp;
Drop table #temp;

Hope this solves your problem.

The Praveen Singh · Answer 2 · 2016-08-25T00:39:00

Solution 2

you can also use Common table Expression for this as follows:-

SQL

WITH T AS
(
SELECT *,
       DENSE_RANK() OVER (ORDER BY OeRT Desc) AS Rnk
FROM TPM_OeeEntry
)
SELECT *
FROM T
WHERE Rnk=1;

Posted 25-Aug-16 0:39am

The Praveen Singh

Comments

ShivAkshay 26-Aug-16 6:28am

With this I am getting only one row of max value in OeRT column which recorded max value in one of the shift. But I want every shift data of max value of OeRT. So I am query the every shift data with parameter of Date, Shift, Location. If meets all these 3 parameter than I am getting some set of rows, from these rows I need max value from OeRT column.

in my stored procedure the parameters are in @sCond. see my stored procedure in my previous question.

Creating parameter like below in my c# project.
string sCond = " and OeLoct=" + cboSector.SelectedValue;
sCond = sCond + " and OeShift='" + cboShftVew.Text + "'";
sCond = sCond + " and CONVERT(DATETIME,OeDate,105)>=CONVERT(DATETIME,'" + OeeDate + "',105)";

The Praveen Singh 1-Sep-16 8:49am

use group by with oeShift.