Query to get the max, min and max-1 in a single query

Question

0.00/5 (No votes)

See more:

Am trying to query a table to get max,min and max-1 values based on IDs.Here i need to get the details of all projects from the subquery 'b' but am getting null values for most of the columns -A.SCAN_ID AS PRIOR_SCAN which is not supposed to be.

What I have tried:

select a.PROJECT,
a.SOLUTION,
a.first_scan,
a.last_scan,
b.PRIOR_SCAN
from
(
SELECT PROJECT,SOLUTION,MIN(SCAN_ID)first_scan, MAX(SCAN_ID)last_scan
FROM PORTAL_CSA.RPT_Scan_Summary
GROUP BY PROJECT,SOLUTION)a
LEFT OUTER JOIN
(
SELECT A.SOA_SECTOR, A.PROJECT,A.SOLUTION, A.SCAN_ID AS PRIOR_SCAN
FROM
(SELECT DISTINCT SOA_SECTOR, PROJECT,SOLUTION, SCAN_ID,
DENSE_RANK() OVER (PARTITION BY PROJECT,SOLUTION ORDER BY SCAN_ID DESC) AS PRIOR_SCAN_ID FROM PORTAL_CSA.RPT_Scan_Summary) A
WHERE A.PRIOR_SCAN_ID = 2 )b ON b.PROJECT =a.PROJECT AND b.SOLUTION =a.SOLUTION

Posted 20-Jul-18 9:01am

GJSS

Updated 21-Jul-18 3:09am

v2

Add a Solution

Comments

MadMyche 20-Jul-18 15:27pm

Can you post some sample data along with the results you expect from that data

GJSS 20-Jul-18 15:38pm

Insert into EXPORT_TABLE (PROJECT,SOLUTION,FIRST_SCAN,LAST_SCAN,PRIOR_SCAN) values ('park.Cash.Trading.csproj','TWSTicketing.sln',2491149,2514275,2514274);
Insert into EXPORT_TABLE (PROJECT,SOLUTION,FIRST_SCAN,LAST_SCAN,PRIOR_SCAN) values ('RBCheck.Services.ArchiveAndPurgeService.csproj','RB.Citi.GlobalCheck.sln',1989546,5385142,5384987);
Insert into EXPORT_TABLE (PROJECT,SOLUTION,FIRST_SCAN,LAST_SCAN,PRIOR_SCAN) values ('Cmb.UI.Framework.Core.csproj','BE2UI.sln',51839,61995,61994);

GJSS 20-Jul-18 15:41pm

Here i want to fetch the column value of A.SCAN_ID AS PRIOR_SCAN for all the projects returning from subquery 'a'.
Rowcount of subquery -15999
rowcount of whole query -16824

Santosh kumar Pithani 21-Jul-18 7:31am

Hello GJSS, use inner join instead of left join in your query.please read about left join you will understood better why you got nulls.

1 solution

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)

Sanwar ranwa · Answer 1 · 2018-07-21T03:09:00

Solution 1

Select max, min, last row for each group in SQL without a subquery[^]

Selecting Max and Min Rows in one SQL Statement[^]

Select min and max values along with other SQL Server columns using T-SQL[^]

Posted 21-Jul-18 3:09am

Sanwar ranwa

Updated 21-Jul-18 3:10am

v2