How to get Min(some column value) with primary key ID and max(some column value) with primary key ID with in a single query or nested query in SQL

Question

I need to get both min and max values of two separate columns with their unique primary key id value.

Anyone ready to help me? Thanks in advance!

    select  ID as ID, min(FROMTIME) as FROMTIME,ID as ID,max(TOTIME) as TOTIME,sum(DIFFERENCE_TIME) as DIFFERENCE_TIME   from ( 
  select PersonalBevorzugteArbeitszeiten.PersonalBevorzugteArbeitszeitID ID, zeitvon  FROMTIME ,ZeitBis  TOTIME,DATEDIFF(SECOND,zeitvon,zeitbis) as DIFFERENCE_TIME  from PersonalBevorzugteArbeitszeiten   join personal on personal.PersonalNr  = PersonalBevorzugteArbeitszeiten.PersonalNr   where personal.PersonalNr  = 5  ) as a group by  ID

Here it's I tried.Can you give a solution for this ? – Mar 09 '18 at 07:23 — , Mar 09 '18 at 07:23

score 0 · Accepted Answer · answered Mar 09 '18 at 06:48

0

You can use a window function in a subquery to get the relevant ordinality of the two columns and then select from this.

with rns as (
   select pk, col1, col2,
   ROW_NUMBER() over (order by col1 desc) rn_max, 
   ROW_NUMBER() over (order by col2 asc) rn_min
)
select pk, 
case when rn_max = 1 then col1 else col2 end as val,
case when rn_max = 1 then 'MAX' else 'MIN' end as max_min 
from rns 
where rn_max = 1 or rn_min = 1

where you can then use RANK() instead or ROW_NUMBER() depending on how you want to handle multiple pks with the same value as per (SQL RANK() versus ROW_NUMBER())

answered Mar 09 '18 at 06:48

NickHilton

662
6
13

I could't get your point...Can you explain and give a piece of example? – Mar 09 '18 at 06:52
The line `ROW_NUMBER() over (order by col1 desc) rn_max` is called a window function. This is a function performed on a window or slice/grouping of your data specified in the `over()` section. So here you are saying give me the `row_number` when you order the data by `col1 descending` You then do a similar thing looking at the max of the other category. You now have a subquery which looks like `pk, col1, col2, ranking of pk for col1 desc, ranking of pk for col2 asc` so you can now select the rows from this query where the rankings = 1 i.e. are the max of col1 or the min of col – NickHilton Mar 15 '18 at 21:52
The case when is then used to switch between the two states for a concise result but its not needed really to pull the information – NickHilton Mar 15 '18 at 21:56

score 0 · Answer 2 · answered Mar 09 '18 at 07:54

0

Consider one table with 3 columns (PID,Price1,Price2) here PID is the primary key

for min value :
1) select PID,Price1 from TableName where Price1 = (select MIN(Price1) from TableName)
2) select top 1 PID,Price1 from TableName order by Price1

for max value :
1) select PID,Price2 from TableName where Price2 = (select MAX(Price2) from TableName)
2) select top 1 PID,Price2 from TableName order by Price2 desc

answered Mar 09 '18 at 07:54

Chetan Ghediya

1
1

I need both Min and Max values of two columns in a single query.You are divides into two distincts. How to make use of it as single one? – Mar 09 '18 at 09:11
You can do it using the subquery, try this query => select top 1 PID AS MaxID, Price1 AS Maxvalue, (select top 1 PID from TableName order by Price2 ) As MinID, (select top 1 Price2 from TableName order by Price2 ) As MinVAlue, from TableName order by Price1 desc – Chetan Ghediya Mar 12 '18 at 06:12

How to get Min(some column value) with primary key ID and max(some column value) with primary key ID with in a single query or nested query in SQL

2 Answers2